Categories
Code

Case Studies – Google Scripts and G Suite

Google Apps Script helps you automate tasks across G Suite and other Google products. Here are some interesting problems that people have solved with Google Scripts and G Suite (formerly known as Google Apps).

Upload Wedding Pictures – We got married and would like the guests to send us any picture they took during the day. I really want to provide them with an easy way to upload the pictures to my google drive. I don’t want to force my guests to create a google account though. [File Upload Forms]

Auto-Confirmation Emails – We’ve have an enquiry form on our hotel website and would like to send an acknowledgement email when a client fills the form. [Form Notifications, Gmail Auto-Responder]

Email Medical Records – I work for a medical group whose e-mail is supported by the gmail platform, and we need a product that can send out attachments that may contain personal health information (PHI). Are you HIPAA compliant? The files are sent directly from your Google Drive via Gmail to the recipient. No other person has any access to your files. [Gmail Mail Merge]

Categories
Code

Singleton Pattern in JavaScript

In the Singleton Pattern in JavaScript, there is exactly one instance of a class and there is no way to create multiple instances of the same class. Singleton objects are generally used to manage global state in an application.

let firebaseInstance = null;
export const getFirebase = () => {
  if (firebaseInstance !== null) {
    return firebaseIntance;
  }
  
  firebase.initializeApp(config);
  firebaseInstance = firebase;
  return firebaseInstance;
}

Using ES6 Classes

let singletonInstance = null;
class Singleton {
  constructor() {
    if (!singletonInstance) {
      singletonInstance = this;
      console.log("Instance created");
    }
    return singletonInstance;
  }
}
const singletonObject = new Singleton();

Using ES7 Classes

class Singleton {
  static singletonInstance = null;
  static getSingletonInstance() {
    if (!Singleton.singletonInstance) {
      Singleton.singletonInstance = new Singleton();
      console.log("Instance created");
    }
    return Singleton.singletonInstance;
  }
}
const singletonObject = Singleton.getSingletonInstance();
Categories
Code

How to Verify Google API OAuth Token

The Creator Studio add-on fetches the OAuth2 access token for the projects using the Google Apps Script API that is then used to authorize the Google Client JavaScript API and fetch the Slide Screenshots.

Unlike the OAuth2 refresh tokens that are forever valid, the access tokens have limited validity (they expire in under 60 minutes) and you should always verify the token before making a server-side request. It will else fail with an error like Invalid Credentials.

Luckily, Google offers a service googleapis.com/oauth2/v1/tokeninfo to check your access tokens and what Google scopes they have access to.

const isOAuthTokenValid = token => {
  const BASE_API = 'https://www.googleapis.com/oauth2/v1/tokeninfo';
  return new Promise((resolve, reject) => {
    fetch(`${BASE_API}?access_token=${token}`, {
      mode: 'cors',
    })
      .then(response => {
        return response.json();
      })
      .then(({ expires_in: timeout = 0 }) => {
        if (timeout > 0) resolve('Token is valid');
        reject(new Error('Token has expired'));
      });
  });
};

export default isOAuthTokenValid;
Categories
Code

Convert Google Slides Presentation to Image Sequence

The Creator Studio add-on for Google Slides can export your Google Slides presentation to a variety of formats including animated GIF images, MP4 video and a sequence of images in PNG format.

Internally, the Slides add-on uses the Google API for Node.js to generate the PNG thumbnails of the presentation and then downloads them using the native Fetch API of the browser.

/* global gapi */

const SIZE = {
  UNSPECIFIED: 'THUMBNAIL_SIZE_UNSPECIFIED',
  LARGE: 'LARGE',
  MEDIUM: 'MEDIUM',
  SMALL: 'SMALL',
};

const IMAGE_SIZE = SIZE.SMALL;
const MAX_SLIDE_COUNT = 3;

const getSlideObjects = presentationId => {
  return new Promise((resolve, reject) => {
    gapi.client.slides.presentations
      .get({
        presentationId,
        fields: 'slides/objectId',
      })
      .then(({ result }) => {
        const pageObjects = result.slides.map(({ objectId }) => objectId);
        resolve(pageObjects.slice(0, MAX_SLIDE_COUNT));
      })
      .catch(err => reject(err));
  });
};

const getThumbnailUrl = (presentationId, pageObjectId) => {
  return new Promise((resolve, reject) => {
    gapi.client.slides.presentations.pages
      .getThumbnail({
        presentationId,
        pageObjectId,
        'thumbnailProperties.mimeType': 'PNG',
        'thumbnailProperties.thumbnailSize': IMAGE_SIZE,
      })
      .then(({ result }) => {
        resolve(result.contentUrl);
      })
      .catch(err => {
        reject(err);
      });
  });
};

const getImageLinks = presentationId => {
  return new Promise((resolve, reject) => {
    getSlideObjects(presentationId)
      .then(pageObjects => {
        return pageObjects.map(pageObjectId => {
          return getThumbnailUrl(presentationId, pageObjectId);
        });
      })
      .then(thumbnailUrls => {
        return Promise.all(thumbnailUrls);
      })
      .then(fileUrls => resolve(fileUrls.filter(url => url)))
      .catch(err => reject(err));
  });
};

export default getImageLinks;

The presentations.pages.getThumbnail method generates a PNG thumbnail image of the specified slide in the Google Presentation and returns a public URL of the thumbnail image.

Please note that getThumbnail is an ‘expensive’ operation and your Google project can only make 100 requests per 100 seconds per user. It is therefore a good idea to cache the results in localStore to avoid hitting the rate limits.

Categories
Code

Download Gmail Attachments to Google Drive with Apps Script

The Save Gmail Attachments add-on for Google Drive downloads file attachments from an email message to a specific folder in your Google Drive. Internally, it uses Google Apps Script and the Advanced Drive Service to fetch the attachments and save them to Drive.

Here’s a sample snippet from the Gmail add-on that shows how you can use GmailApp service with Google Drive in Apps Script to create a copy of any Gmail attachment inside Google Drive.

We’ve set the includeAttachments option to true and includeInlineImages to false to only download regular (non-inline) attachments and not inline images.

export const saveGmailtoGoogleDrive = () => {
  const folderId = 'Google_Drive_Folder_Id';
  const searchQuery = 'has:attachments';
  const threads = GmailApp.search(searchQuery, 0, 10);
  threads.forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      const attachments = message.getAttachments({
          includeInlineImages: false,
          includeAttachments: true
      });
      attachments.forEach(attachment => {
        Drive.Files.insert(
          {
            title: attachment.getName(),
            mimeType: attachment.getContentType(),
            parents: [{ id: folderId }]
          },
          attachment.copyBlob()
        );
      });
    });
  });
};
Categories
Code

How to Get Hidden and Filtered Rows in Google Sheets with Google Script

You can hide entire rows in Google Sheets manually or use filters and hide on or more or rows in the sheet that matches your specified criteria. For instance, if you have a sheet containing orders from different countries, you can set up a country filter to hide all rows where the country is not the United States.

If you have a Google Script that iterates through each row in the Google Sheet for performing actions on the row, like sending emails or merging documents, you can check for the hidden and filtered rows and easily skip them from the workflow.

Also, a row in the Google Sheet can be filtered and hidden at the same time.

function getHiddenAndFilteredRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var d=0; d
Categories
Code

Create YouTube Playlist with Google Script and Sheets

This Google Scripts reads the list of YouTube Video IDs from a Google Spreadsheet and adds them all to an existing playlist in YouTube.

Please enable the YouTube API under Advanced Google Services and inside the corresponding Google Cloud Platform project. You can also create YouTube playlist by appending the video IDs to a special URL.

function addVideoToYouTubePlaylist() {
  // Read the source videos from Google Sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  // Add your own playlist Id here
  var playlistId = "PLAYLIST_ID_HERE";
  
  // iterate through all rows in the sheet
  for (var d=1,l=data.length; d

		
Categories
Code

Search YouTube with Google Script and YouTube API

This Google Script shows how to query the YouTube API to search videos by keywords. The source data is in a column in Google Spreadsheet and we connect to the YouTube Data API via Google Apps Script. The YouTube service should be in enabled in the Google Cloud Platform project.

function findYouTubeVideoByKeywords() {
  // get the search query from Google Sheets
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  // iterate through all rows in the sheet
  for (var d=1,l=data.length; d

		
Categories
Code

How to Check if the Google User has a G Suite Account

This Google Apps Script will help your add-on determine whether the current logged-in user is on GSuite (Google Apps) or if they are using the consumer (free) Google Account. G Suite users have higher email sending limits and have more storage space in Google Drive vis-a-vis free accounts.

The code snippet is courtesy +FaustinoRodriguez.

function isGSuiteUser() {
  
  var url = "https://www.googleapis.com/oauth2/v2/userinfo";
  var oAuthToken = ScriptApp.getOAuthToken();
  
  var params = {
    "method": "GET",
    "headers": {
      "Authorization": "Bearer " + oAuthToken
    },
    muteHttpExceptions: true
  };
  
  var response = UrlFetchApp.fetch(url, params);  
  var userInfo = JSON.parse(response);
  
  if (userInfo.hasOwnProperty("hd")) {
    return userInfo.email + " is using GSuite";
  }
  
  return userInfo.name + " is not using G Suite";

}

G Suite (Google Apps) users will have the hd attribute set to true while is not available for consumer account. One more thing – you’ll only know if a user belongs to G Suite domain, it won’t saying anything about the version of G Suite that a user has subscribed to.

A user could be part of basic GSuite (or the legacy version of Google Apps) or they could be on G Suite enterprise, the response would be the same.

Categories
Code

Get List of Google Team Drives with Apps Script

This Google Apps Script returns a list of Team Drives that the authorized user is part of. The code is written in ES6 and you would need to transpile the code using Babel before pushing it via Google Clasp.

const makeQueryString = (url, params = {}) => {
  const paramString = Object.keys(params)
    .map(
      (key) => `${encodeURIComponent(key)}=${encodeURIComponent(params[key])}`,
    )
    .join('&');
  return url + (url.indexOf('?') >= 0 ? '&' : '?') + paramString;
};

const makeHttpGetRequest = (apiUrl, params, accessToken) => {
  const url = makeQueryString(apiUrl, params);
  const response = UrlFetchApp.fetch(url, {
    headers: {
      Authorization: `Bearer ${accessToken}`,
    },
    muteHttpExceptions: true,
  });
  return JSON.parse(response);
};

const getTeamDrivesForUser = () => {
  const params = {
    pageSize: 100,
    useDomainAdminAccess: true,
  };
  const data = [];
  const accessToken = ScriptApp.getOAuthToken();
  const API = 'https://www.googleapis.com/drive/v3/teamdrives';

  do {
    let response = makeHttpGetRequest(API, params, accessToken);

    if (response.teamDrives) {
      response.teamDrives.forEach((td) => {
        data.push([td.id, td.name]);
      });
    }

    params.pageToken = response.nextPageToken || null;
  } while (params.pageToken);

  Logger.log(data);
};
Categories
Code

What are Closures in JavaScript

Closure in JavaScript, according to Douglas Crockford, is an inner function that always has access to outer function’s variables and parameters, even after the outer function has returned. The inner nested function has access to the outer function’s parameters but cannot call the outer function’s arguments object.

Let’s illustrate closures with a simple example.

function getCurrentDate() {
  var date = new Date();
  return date.toISOString();
}

console.log(getCurrentDate());

setTimeout(function() {
  console.log(getCurrentDate());
}, 2000);

In the above function, we are printing the current date to the console. The method is invoked twice, after a delay of few seconds, and the date string would be different in each call.

JavaScript Closure

With Closures, the date variable would stick around even after the function has returned and thus we are able to create a container for our variable. Here’s the closure version of the same function.

function dateClosure() {
  var date = new Date();  
  return function() {
    return date.toISOString();
  };
}

// Instantiate the function
var myClosure = dateClosure();

console.log(myClosure());

setTimeout(function() {
  console.log(myClosure());
}, 2000);
 

Run the function and you’ll get the same value for the date string every single time. To recap, closure is when a function remembers the variables around it even when the function has executed and returned the value.

Here’s another example of a simple closure. By referencing the variable count, the inner function gets a closure over the variable and thus it is going to be preserved even after we return the function. You can call the returned function multiple time and it will increment the count each time.

function counter() {
  var count = 0;
  return function() {
    return count++;
  };
}

var myCounter = counter();

console.log(myCounter());
console.log(myCounter());

Here’s another pattern for defining closures.


var counter = function() {
  
  var count = 0;
  
  // Nested function
  // Closure created and the variable is preserved in memory
  var getCounter = function() {
    return count++;
  };
  
  // returns a reference to the inner function
  return {
    val: getCounter
  }
  
};

var myCounter = new counter();
console.log(myCounter.val());
console.log(myCounter.val());

In the next example, we declare a function that takes a parameter x and returns a function that closes over the variable. The value of x for the add2 function will always be 2.

function sum(x) {
  return function(y) {
    return x+y;
  };
}

var add2 = sum(2);

console.log(add2(5));
console.log(add2(10));

In essence, whenever you nest a function inside another function, a closure is used.

Closures are a way to let a function have persistent (the value is preserved even after the function has executed and returned) and private variables (the variables are local to the function) without polluting the global namespace.

Categories
Code

Generate Screenshots of Google Slides with Google Script

The new TallTweets app uses the Google Apps Script to convert Google Apps Script into GIF images. Internally, it uses the Google Slides API for generating screenshot images of individual slides of the presentation before stitching them into a GIF.

This Google Apps Script function uses the Advanced Slides API to generate high-resolution thumbnail images of the slides and uploads the individual slides to the Google Drive of the current user.

The public URLs of screenshot images are accessible for 30 minutes post which the URLs will expire automatically. The default mime type of the thumbnail image is PNG.

function generateScreenshots(presentationId) {
  var presentation = SlidesApp.openById(presentationId);
  var baseUrl =
    "https://slides.googleapis.com/v1/presentations/{presentationId}/pages/{pageObjectId}/thumbnail";
  var parameters = {
    method: "GET",
    headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
    contentType: "application/json",
    muteHttpExceptions: true
  };

  // Log URL of the main thumbnail of the deck
  Logger.log(Drive.Files.get(presentationId).thumbnailLink);

  // For storing the screenshot image URLs
  var screenshots = [];

  var slides = presentation.getSlides().forEach(function(slide, index) {
    var url = baseUrl
      .replace("{presentationId}", presentationId)
      .replace("{pageObjectId}", slide.getObjectId());
    var response = JSON.parse(UrlFetchApp.fetch(url, parameters));

    // Upload Googel Slide image to Google Drive
    var blob = UrlFetchApp.fetch(response.contentUrl).getBlob();
    DriveApp.createFile(blob).setName("Image " + (index + 1) + ".png");

    screenshots.push(response.contentUrl);
  });

  return screenshots;
}
Categories
Code

Find Driving Directions, Distance and Time with Google Maps API and Apps Script

Use Google Apps Script with the Google Maps API to find the driving distance, time and the driving route between any two points. The origin and destination may either be specified as plain text or you can specify the latitude and longitude coordinates. Google Maps API can fetch directions for WALKING, DRIVING and BICYCLING modes.

function GoogleMapsAPI () {
  
  // Written by Amit Agarwal
  // Web: labnol.org

  var origin = "10 Hanover Square, NY 10005";
  var destination = "Times Square";
  var travelMode = Maps.DirectionFinder.Mode.WALKING;
  
  var directions = Maps.newDirectionFinder()
  .setOrigin(origin)
  .setDestination(destination)
  .setMode(travelMode)
  .getDirections();
  
  if (directions.status !== "OK") 
    return "Error: " + directions.status;
  
  var route = directions.routes[0].legs[0];
  var time = route.duration.text;
  var distance = route.distance.text;
  var steps = route.steps.map(function(step) {
    return step.html_instructions.replace(/<[^>]+>/g, "")
  }).join("\n");
  
  Logger.log(steps);

}
Categories
Code

Upload Google Drive Files to Dropbox with Google Apps Script

Learn how to upload any Google Drive file to your Dropbox folder using Google Apps Script. To get started, create a new Dropbox app, as explained in the previous Dropbox example, and get the access token to skip the OAuth2 workflow.

Also see: Upload Drive Files to Google Cloud Storage

function uploadGoogleFilesToDropbox(googleDriveFileId) {
  
  var parameters = {
    "path": "/PDF/labnol.pdf",
    "mode": "add",
    "autorename": true,
    "mute": false
  };
  
  // Add your Dropbox Access Token
  var dropboxAccessToken = 'labnolorg-MZmqX';  
  
  var headers = {
    "Content-Type": "application/octet-stream",
    'Authorization': 'Bearer ' + dropboxAccessToken,
    "Dropbox-API-Arg": JSON.stringify(parameters)
  };
  
  var driveFile = DriveApp.getFileById(googleDriveFileId);
  
  var options = {
    "method": "POST",
    "headers": headers,
    "payload": driveFile.getBlob().getBytes()
  };
  
  var apiUrl = "https://content.dropboxapi.com/2/files/upload";
  var response = JSON.parse(UrlFetchApp.fetch(apiUrl, options).getContentText());
  
  Logger.log("File uploaded successfully to Dropbox");
  
}
Categories
Code

Download Web Files to Dropbox with Google Apps Script

Use Google Apps Script with the Dropbox API to download any file from the Internet and upload it directly to the Internet without saving it to your computer. You need to specify the URL of the web file and path in your Dropbox folder where that file should be saved. If a file in the given path already exists, the new file will be renamed.

To get started, sign-in to your Dropbox account and create a new Dropbox app as shown in the screenshot below.

Go to the next Dropbox screen and create an access token. By generating an access token, you will be able to make Dropbox API calls for your own account without going through the Dropbox OAuth2 authorization flow. To obtain access tokens for other users, use the standard OAuth flow.

function saveWebFilesToDropbox(fileUrl) {
  
  var accessToken = 'Dropbox-xxxx-1234';  
  var headers = {
    "Content-Type": "application/json",
    'Authorization': 'Bearer ' + accessToken
  };
  
  fileUrl = fileUrl || "https://img.labnol.org/files/Most-Useful-Websites.pdf";
  var parameters = {
    "url": fileUrl,
    "path": "/PDF/book.pdf"
  };
  
  var options = {
    "method": "POST",
    "headers": headers,
    "payload": JSON.stringify(parameters)
  };
  
  var apiUrl = "https://api.dropboxapi.com/2/files/save_url";
  var response = JSON.parse(UrlFetchApp.fetch(apiUrl, options).getContentText());
  
  var checkUrl = apiUrl + "/check_job_status";  
  var checkOptions = {
    "method": "POST",
    "headers": headers,
    "payload": JSON.stringify({
      "async_job_id": response.async_job_id
    })
  };
  
  do {
    Utilities.sleep(1000);
    response = JSON.parse(UrlFetchApp.fetch(checkUrl, checkOptions).getContentText());
  } while (response[".tag"] != "complete");
  
  Logger.log("File uploaded successfully to Dropbox");
  
}

The /save_url endpoint saves the file at the specified URL in your Dropbox. It returns a Job ID since the upload process is asynchronous. You can make calls to /save_url/check_job_status to check the upload status when the return code is “complete”, the file has been successfully uploaded to your Dropbox folder.

The SaveUrl functionality in Dropbox doesn’t have a file size limit, but the download operations on the Dropbox servers do time out after 5 minutes. So, if it takes longer than 5 minutes to transfer the file from the URL to the Dropbox servers, the file won’t be saved.

If you call /save_url/check_job_status again to check later (e.g., after at most 5 minutes) it should return either information about the successfully saved file, or an error message indicating the issue.

Categories
Code

Extract Text from PDF files with Google Apps Script

You can use Google Apps Script to extract text from a PDF file and save the extracted text as a new document in Google Drive. The document will also retain the simple formatting of the PDF file.

The following script illustrates how to use the Google Drive API as an OCR engine and extract text from a PDF file on the Internet. The code can be modified to convert PDF files existing in Google Drive to an editable document.

function extractTextFromPDF() {
  
  // PDF File URL 
  // You can also pull PDFs from Google Drive
  var url = "https://img.labnol.org/files/Most-Useful-Websites.pdf";  
  
  var blob = UrlFetchApp.fetch(url).getBlob();
  var resource = {
    title: blob.getName(),
    mimeType: blob.getContentType()
  };
  
  // Enable the Advanced Drive API Service
  var file = Drive.Files.insert(resource, blob, {ocr: true, ocrLanguage: "en"});
  
  // Extract Text from PDF file
  var doc = DocumentApp.openById(file.id);
  var text = doc.getBody().getText();
  
  return text;
}

Google Drive API can perform OCR on JPG, PNG, GIF and PDF files. You can also specify the ocrLanguage property to specify the language to use for OCR.

Combine this with the doGet method and you’ve made an HTTP Rest API that can perform OCR on any web document with a simple GET request. This can be modified to work with file upload forms as well.

Categories
Code

Post Messages to Discord Channel via Google Apps Script & Webhooks

Discord a hugely popular voice and text chat app for gamers. Like Slack, it offers text chat channels to help you stay connected with the community. The following example shows how you can easily post text messages and status updates to your Discord channels (server) using webhooks and Google Apps Script.

To get started, go to your Discord channel, choose settings, webhooks and click the “Create Webhook” button. Make a note of the webhook URL. Now all you need to do is post a JSON message by making an HTTP POST request to the webhook URL to post a message to the channel.

function postMessageToDiscord(message) {

  message = message || "Hello World!";
  
  var discordUrl = 'https://discordapp.com/api/webhooks/labnol/123';
  var payload = JSON.stringify({content: message});
  
  var params = {
    headers: {
      'Content-Type': 'application/x-www-form-urlencoded'
    },
    method: "POST",
    payload: payload,
    muteHttpExceptions: true
  };
  
  var response = UrlFetchApp.fetch(discordUrl, params);
  
  Logger.log(response.getContentText());

}

You can post Gmail messages to your Discord channel, new tweets, YouTube subscriptions, weather updates, Bitcoin price updates and anything else to Discord via Webhooks and Google Apps Script.

Categories
Code

Tutorial: Google Cloud Speech API with Service Account

This tutorial explains how to use the Google Cloud Speech API with Google Apps Script. We’ll use a Service Account to authenticate the application to the Cloud Speech API and the source audio file is stored in a Google Cloud Storage bucket.

The application uses the asynchronous speech recognition mode since the input audio is longer than a minute.

Step 1: Enable Cloud Speech API

Create a new Google Apps Script project, go to Resources > Cloud Platform Project to open the associated project in the Google Developers Console. Go to Libraries and enable the Cloud Speech API.

service-account-key.png

Step 2: Create Google Service Account

Go to the Credentials tab, create credentials and choose Service Account from the drop down. Set the service account role as project owner and save the JSON private key file to your Google Drive.

Step 3: Run the Code

Paste this code in your Google Apps Script editor. Remember to change the location of the audio file in Google Cloud Storage and the location of the service account key in Google Drive.

/* 

Written by Amit Agarwal
email: amit@labnol.org
web: https://digitalinspiration.com
twitter: @labnol

*/

// Get the service account private keys from Google Drive
function getServiceAccountKeys() {
    var fileLink = "https://drive.google.com/open?id=ctrlq....";
    var fileId = fileLink.match(/[\w-]{25,}/)[0];
    var content = DriveApp.getFileById(fileId).getAs("application/json").getDataAsString();
    return JSON.parse(content);
}

// Create the Google service
function getGoogleCloudService() {
    var privateKeys = getServiceAccountKeys();
    return OAuth2.createService('GoogleCloud:' + Session.getActiveUser().getEmail())
        // Set the endpoint URL.
        .setTokenUrl('https://accounts.google.com/o/oauth2/token')
        // Set the private key and issuer.
        .setPrivateKey(privateKeys['private_key'])
        .setIssuer(privateKeys['client_email'])
        // Set the property store where authorized tokens should be persisted.
        .setPropertyStore(PropertiesService.getScriptProperties())
        // Set the scope. 
        .setScope('https://www.googleapis.com/auth/cloud-platform');
}

// Initialize an async speech recognition job
function createRecognitionJob() {
    var service = getGoogleCloudService();
    if (service.hasAccess()) {
        var accessToken = service.getAccessToken();
        var url = "https://speech.googleapis.com/v1/speech:longrunningrecognize";
        var payload = {
            config: {
                languageCode: "en-US"
            },
            audio: {
                uri: "gs://gcs-test-data/vr.flac"
            }
        };
        var response = UrlFetchApp.fetch(url, {
            method: 'POST',
            headers: {
                Authorization: 'Bearer ' + accessToken
            },
            contentType: "application/json",
            payload: JSON.stringify(payload)
        });
        var result = JSON.parse(response.getContentText());
        Utilities.sleep(30 * 1000);
        getTranscript(result.name, accessToken);
    }
}

// Print the speech transcript to the console
function getTranscript(name, accessToken) {
    var url = "https://speech.googleapis.com/v1/operations/" + name;
    var response = UrlFetchApp.fetch(url, {
        method: 'GET',
        headers: {
            Authorization: 'Bearer ' + accessToken
        }
    });
    var result = JSON.parse(response.getContentText());
    Logger.log(JSON.stringify(result, null, 2));
}

Authorize the code and, if all the permissions are correctly setup, you should see the audio transcript in your console window as shown below.

cloud-speech-api.png

 

Categories
Code

How to Use the Google Natural Language API with Apps Script

Google Natural Language API helps you make sense of unstructured data. You can pass a string, like a tweet or transcribed speech, to the Natual Language API and it will detect the entities (like person, places, products, events), the sentiment (whether customers are happy or mad at your brand), and the syntax (parts of speech).

The Cloud Natural Language API can analyze sentences in multiple languages and it has a REST API so you can easily use it with your Google Apps Script projects. For instance, the Twitter Archiver add-on saves tweets in a Google Sheet. NLP API can be used to understand the emotion or sentiments in a tweet to determine the satisfaction level of customers on social media.

To get started, go to script.google.com and create a new project. Then go to Resources – Cloud Platform Project to open Google Developers Console. Here go to the API section and enable the Natular Language API under Google Cloud Machine Learning. Next click on Credentials to create an API key for your Google Script.

function analyzeText() {
  
  var text = "The quick brown fox jumped over the lazy dog";
  
  var requestUrl = [
    'https://language.googleapis.com/v1/documents:analyzeSentiment?key=',
    "THIS_IS_THE_API_KEY"
  ].join("");
  
  // Use documents:analyzeEntities API endpoint for analyzing entities
  // Use documents:analyzeSyntax API endpoint for synctactic (linguistic) analysis
  
  var data = {
    "document": {
      "language": "en-us",
      "type": "PLAIN_TEXT",
      "content": text
    },
    "encodingType": "UTF8"
  };
  
  var options = {
    method : "POST",
    contentType: "application/json",
    payload : JSON.stringify(data)
  };
  
  var response = UrlFetchApp.fetch(requestUrl, options);
  
  var data = JSON.parse(response);
  
  Logger.log(data);
  
}

Things to know:

  1. If you don’t specify document.language, then the language will be automatically detected.
  2. You can upload the text file to Google Cloud Storage and specify the URI without the need to send the contents of the file in the body of your request.
  3. Google Cloud Natural Language API requires billing to be enabled.
Categories
Docs

Unsubscribe from Summary of Failure Emails from Google Apps Script

You may sometimes receive a “Summary of failures for Google Apps Script” email notification from Google (apps-scripts-notifications@google.com). The error messages may say:

  • Exceeded maximum execution time
  • Service using too much computer time for one day
  • Authorization is required to perform that action
  • Service using too much computer time for one day

Google Scripts run on Google cloud servers and Google allows fixed CPU quota (time) to a script per user. These time quotas are different for Gmail (90 minutes/day) and Google Apps (3 hours/day) and the daily quotas are refreshed at the end of a 24-hour window.

If you get any of these service errors from Google, it indicates that the script exceeded the total allowable execution time for one day. The script execution is temporarily stopped and the execution is resumed as soon as Google resets your daily quota.

For “authorization required” emails, it likely indicates that the script no longer has permission to access your Google account and you’d need to reauthorize the script to get it working again. Until the script is reauthorized, the notifications emails would continue to arrive.

How to Stop Failure Emails from Google Script

If you would like to prevent Google from sending these failure emails permanently, you need to uninstall the associated Google Script or Google Add-on from your Google account. You will also have to remove the time-based trigger it established to stop the email notifications.

Here’s how:

Step 1: Open a new tab in your browser and open script.google.com to launch the Google Apps Script Editor. You need to sign-in with the same Google Account that is receiving the email notification errors.

Step 2: Go to the Edit Menu and choose All your triggers.

Step 3: Apps Script will ask you to name the script you are using. Just click ‘OK’.

Step 4: The popup window will display a list of all triggers associated with the various Google Scripts that you have authorized in the past. Look through the list for the trigger that is failing.

Step 5. Click the ‘x’ icon next to the trigger and click “Save” to remove the trigger from your account.

The trigger should now be removed, which should, in turn, stop the email notifications from apps-scripts-notifications@google.com – you might receive one or two more after completing this process since the notifications are batched together.

You can close the script editor tab. Note that this process described above will create an Apps Script file called “Untitled project” in your root Drive folder — once the trigger is deleted, you can remove this file as well.

Categories
Code

Convert Audio to Text with Google Cloud Speech API

The Online Dictation app uses the HTML5 Speech Recognition API to transcribe your voice into digital text. If you have a pre-recorded audio file, you can turn on speech recognition inside Dictation, play the audio file and get the speech as text (see demo).

Google offers a Cloud Speech API for developers to convert audio to text. You can upload the audio file in FLAC format to Google Cloud storage and the speech API will transcribe the audio to text. If you have audio in MP3 format, use the FFMpeg tool for converting the audio to the desired format.

Also see: Cloud Speech API with Google Service Account

In this example, we upload the .flac audio file to Google Drive (for those who don’t have Google Cloud Storage) and call the Cloud Speech API via the UrlFetchApp service. You need to enable billing in your Google Cloud console, enable the Speech API and also setup an API Key or a service account.


/* 

Written by Amit Agarwal
email: amit@labnol.org
web: https://digitalinspiration.com
twitter: @labnol

*/

function convertAudioToText(flacFile, languageCode) {
  
  var file = DriveApp.getFilesByName(flacFile).next();
  var bytes = file.getBlob().getBytes();
  
  var payload = {
    config:{
      encoding: "LINEAR16",
      sampleRate: 16000,
      languageCode: languageCode || "en-US"
    },
    audio: {
      // You may also upload the audio file to Google 
      // Cloud Storage and pass the object URL here
      content:Utilities.base64Encode(bytes)
    }
  };
  
  // Replace XYZ with your Cloud Speech API key
  var response = UrlFetchApp.fetch(
    "https://speech.googleapis.com/v1/speech:recognize?key=XYZ", {
      method: "POST",
      contentType: "application/json",
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });
  
  Logger.log(response.getContentText());
  
}

Here’s another example that uses the CURL library to send speech recognition requests from the command line.

curl --silent --insecure --header "Content-Type: application/json"
"https://speech.googleapis.com/v1/speech:recognize?key=XYZ"  
--data @payload.json 

// Content of payload.json
  {
    "config": {
        "encoding":"FLAC",
        "sampleRate": 16000,
        "languageCode": "en-US"

    },
    "audio": {
        "uri":"gs://ctrlq.org/audio.flac"
    }
  }
Categories
Code

Add the Edit Response URL of the Google Form to Google Spreadsheet

This Google Apps Script code will insert the Form Edit Response URL to the Google Spreadsheet when a new Google Form is submitted. The response URL is added to the same spreadsheet that is collecting the responses for the Google Form.

The code is inserting the raw link here but you can use the built-in HYPERLINK() formula to create a clickable hyperlink inside the spreadsheet cell. The script function can also be integrated with the Google Shortener API (or Bitly) to insert a short URL that is easier to copy-paste and share.


/*
* Written by Amit Agarwal
* Web: digitalinspiration.com
* Email: amit@labnol.org
* MIT License 
*/

// Create the Form Submit Trigger
function createFormTrigger() {
  var triggerName = "addFormResponseUrl";
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger(triggerName)
    .forSpreadsheet(spreadsheet)
    .onFormSubmit()
    .create();
}

function addFormResponseUrl(e) {
  
  // Get the Google Form linked to the response
  var responseSheet = e.range.getSheet();
  var googleFormUrl = responseSheet.getFormUrl();
  var googleForm = FormApp.openByUrl(googleFormUrl);
  
  // Get the form response based on the timestamp
  var timestamp = new Date(e.namedValues.Timestamp[0]);
  var formResponse = googleForm.getResponses(timestamp).pop();
  
  // Get the Form response URL and add it to the Google Spreadsheet
  var responseUrl = formResponse.getEditResponseUrl();
  var row = e.range.getRow();
  var responseColumn = 10; // Column where the response URL is recorded.
  responseSheet.getRange(row, responseColumn).setValue(responseUrl);
}

Please note that anyone who has access to this unique Form Response Edit URL can change the form response. The getEditResponseUrl() method will return the URL to edit a response that has already been submitted, even if the Form.setAllowResponseEdits(enabled) setting is disabled.

Also, you should always get the URL of the linked Google Form via the active sheet and not the active spreadsheet as there could multiple forms associated with the same Google Spreadsheet.

Categories
Code

Web Scrape Instagram to Get User Profiles with Google Apps Script

This example shows how to screen scrape the Instagram website to extract the profile information and statistics of any user on Instagram. You can get the user’s name, profile photos, follower count, friends count, website URL and other details with the script.

Instagram provides a secret URL that returns the profile of any user in JSON format (see Instagram JSON file). Web apps can connect to this URL to fetch profile details without requiring OAuth tokens or any other authorization. The URLs work for both public and private user profiles on Instagram.

function getInstagramStatistics(user) {
  
  user = user || "labnol";
  
  var url = "https://www.instagram.com/u/?__a=1";
  
  var result = UrlFetchApp.fetch(url.replace("u", user), {
    muteHttpExceptions: true
  });
  
  if (result.getResponseCode() === 200) {
    var json = JSON.parse(result.getContentText()).user;
    var data = {
      screen_name: json.username,
      full_name: json.full_name,
      is_private: json.is_private,
      is_verified: json.is_verified,
      profile_image: json.profile_pic_url,
      website_url: json.external_url,
      follower_count: json.followed_by.count,
      friend_count: json.follows.count      
    }
    return data;
  } else {
    Logger.log("User not found");
    return null;
  }
  
}
Categories
Code

Using Zoho API with Google Apps Script

This example shows how to connect to Zoho Books using Google Apps Script and the Zoho APIs. The HTTP requests to the Zoho Books API are authenticated using an authtoken. You can go to the following URL to generate an auth token for your Google Apps Script project.

https://accounts.zoho.com/apiauthtoken/create?SCOPE=ZohoBooks/booksapi

You’d also need the organization id that can be found at this URL.

https://books.zoho.com/app#/organizations

This function creates a new contact inside Zoho Books using Google Apps Script. You can extend it to export your Google Contacts to Zoho Books, setup a form submit trigger to create contacts from Google Form submissions and so on.


function Zoho() {
  
  var contact = {
    "contact_name": "Amit Agarwal",
    "company_name": "Digital Inspiration",
    "website": "digitalinspiration.com",
    "twitter": "labnol",
    "contact_persons": [
      {
        "salutation": "Mr",
        "first_name": "Amit",
        "last_name": "Agarwal",
        "email": "amit@labnol.org",
        "is_primary_contact": true
      }
    ]
  };
  
  var zohoOauthToken = "xxx";
  var zohoOrganization = "yyy";
  
  var zohoUrl = [ 
    "https://books.zoho.com/api/v3/contacts?",
    "organization_id=", zohoOrganization,
    "&authtoken=", zohoOauthToken,
    "&JSONString=", encodeURIComponent(JSON.stringify(contact))    
  ].join("");
    
  try {
    var response = UrlFetchApp.fetch(zohoUrl, {
      method: "POST",
      muteHttpExceptions: true
    });
    var result = JSON.parse(response.getContentText());
    Logger.log(result.message);
  } catch (error) {
    Logger.log(error.toString());
  }
  
}
Categories
Code

How to Use Google Cloud APIs with Apps Script – Sample Application

The Google Cloud Vision API helps you identify text, objects and places inside pictures. The images may be hosted on a public website, you could store them inside a Google Cloud Storage bucket or you can encode the images to a base64 string.

This sample applications will help you understand how to interact with the Google Cloud Vision API using Google Apps Script. To get started, create a new Google Script. Go to Resources > Cloud Platform Project > View API Console and enable the Google Cloud Vision API.

Also see: Dummies Guide to Google OAuth 2
Inside the Google APIs dashboard, go to Credentials > Create Credentials > OAuth Client ID and choose Web Application as the type of application. Put https://script.google.com under Authorized JavaScript Origins.

For the Authorized Redirect URIs, go to the Script, run getGoogleCallbackUrl and you will find the URL inside the logs section.

// 1. Use this Callback Url with your Google Project
function getGoogleCallbackURL(silent) {
  var url = ScriptApp.getService().getUrl();
  var callbackUrl = (url.indexOf('/exec') >= 0 ? url.slice(0, -4) : url.slice(0, -3)) + 'usercallback';
  if (!silent) Logger.log(callbackUrl);
  return callbackUrl;
}

Save the Oauth2 Client and make a note of the Google Client Id and the Client Secret. Put them in the storeGoogleCredentials() function, run the function to save the credentials in the property store and then remove the values from the script.

// 2. Store the Client ID and Client Secret in the Property Store
function storeGoogleCredentials() {    
  resetSettings_();
  getPropertyStore_().setProperties({
    "client_id": "123.apps.googleusercontent.com",
    "client_secret": "googleClientSecret"
  });
}

Publish the script as a web app and open the app URL in a new tab. It will require authorization once and then store the refresh token in the property store.

// 3. Get the Oauth URL to authorize the app
function doGet(e) {
  
  var propertyStore = getPropertyStore_();
  
  if (!propertyStore.getProperty('refresh_token')) {
    
    var stateToken = ScriptApp
    .newStateToken()
    .withMethod('googleCallback')
    .withArgument('name', 'value')
    .withTimeout(2000)
    .createToken();
    
    var params = {
      state: stateToken,
      scope: [
        "https://www.googleapis.com/auth/cloud-platform", 
        "https://www.googleapis.com/auth/cloud-vision"
      ].join(" "),
      client_id: propertyStore.getProperty('client_id'),
      redirect_uri: getGoogleCallbackURL(true),
      response_type: 'code',
      access_type: 'offline',
      approval_prompt: 'force'
    };
    var queryString = Object.keys(params).map(function (e) {
      return e + '=' + encodeURIComponent(params[e]);
    }).join("&");
    
    var url = 'https://accounts.google.com/o/oauth2/auth?' + queryString;
    return HtmlService.createHtmlOutput("Click here to authorize".replace("URL", url));
  } else {
    return HtmlService.createHtmlOutput("ctrlq.org app is authorized");
  }
}

// Exchange Authorization code with Access Token
function googleCallback(e) {
  
  var propertyStore = getPropertyStore_();
  var props = propertyStore.getProperties();
  
  var credentials = makeHttpPostRequest_(
    "https://accounts.google.com/o/oauth2/token", {
      code: e.parameter.code,
      redirect_uri: getGoogleCallbackURL(true),
      client_id: props.client_id,
      client_secret: props.client_secret,
      grant_type: "authorization_code"
    });
  
  if (!credentials.error) {
    cacheAccessToken_(credentials.access_token);
    propertyStore.setProperty('refresh_token', credentials.refresh_token);
    return HtmlService.createHtmlOutput("OK");
  }
  
  return HtmlService.createHtmlOutput(credentials.error);
}

If you get an invalid_scope error saying “You don’t have permission to access some scopes. Your project is trying to access scopes that need to go through the verification process.” – you’ll have to submit a request using our OAuth Developer Verification form.

The access token is stored in the cache as it is valid for 3600 seconds and a new token can be requested using the refresh token.

// The access token is in cache and can be requested using the refresh token
function getAccessToken_() {
  var accessToken = getCacheStore_().get("access_token");
  if (!accessToken) {
    accessToken = refreshAccessToken_();
  }
  return accessToken;
}

function cacheAccessToken_(accessToken) {
  // Cache for 55 minutes, token otherwise valid for 60 minutes
  getCacheStore_().put("access_token", accessToken, 3300);
}

function refreshAccessToken_() {
  
  var props = getPropertyStore_().getProperties();
  var response = makeHttpPostRequest_(
    "https://accounts.google.com/o/oauth2/token", {
      client_id: props.client_id,
      client_secret: props.client_secret,
      refresh_token: props.refresh_token,
      grant_type: "refresh_token"
    });
  
  if (response.hasOwnProperty("access_token")) {
    cacheAccessToken_(json.access_token);
    return json.access_token;
  }
  
  return null;  
}

Now that our basic setup is in place, we can make a call to the Cloud Vision API with a simple HTTP POST request. The authorization headers should include the bearer access token.

function CloudVisionAPI(imageUrl) {
  var imageBytes = UrlFetchApp.fetch(imageUrl).getContent();
  var payload = JSON.stringify({
    requests: [{
      image: {
        content: Utilities.base64Encode(imageBytes)
      },
      features: [{
          type: "LABEL_DETECTION", 
          maxResults: 3
      }]
    }]
  });
  
  var requestUrl = 'https://vision.googleapis.com/v1/images:annotate';
  var response = UrlFetchApp.fetch(requestUrl, {
    method: 'POST',
    headers: {
      authorization: 'Bearer ' + getAccessToken_()
    },
    contentType: 'application/json',
    payload: payload,
    muteHttpExceptions: true
  }).getContentText();

  Logger.log(JSON.parse(response));

}

The refresh token will remain valid until access hasn’t been revoked by the user.

function revokeAccess() {
  var propertyStore = getPropertyStore_();
  var accessToken = getAccessToken_();
  if (accessToken !== null) {
    var url = "https://accounts.google.com/o/oauth2/revoke?token=" + accessToken;
    var res = UrlFetchApp.fetch(url, {
      muteHttpExceptions: true
    });
  }
  resetSettings_();
}

And here are a couple of helper utility functions for accessing the cache and property store.


function getCacheStore_() {
  return CacheService.getScriptCache();
}

function getPropertyStore_() {
  return PropertiesService.getScriptProperties();
}

function resetSettings_() {
  getPropertyStore_().deleteAllProperties();
  getCacheStore_().remove("access_token")
}

function makeHttpPostRequest_(url, payload) {
  try {
    var response = UrlFetchApp.fetch(url, {
      method: "POST",
      payload: payload,
      muteHttpExceptions: true
    }).getContentText();  
    return JSON.parse(response);
  } catch (f) {
    Logger.log(f.toString());
  }
  return {};
}

The access tokens expire every 60 minutes. You can also make an HTTPS POST or GET request to the tokeninfo endpoint to know about the validity, scope and expiry of the token.

googleapis.com/oauth2/v3/tokeninfo?access_token=ACCESSTOKEN
Categories
Code

Build a REST JSON API With Google Apps Script

This example shows how to build a simple JSON REST API with Google Apps Script. You can make a jQuery AJAX request to the Google Script and the server returns a JSON response. The server side code can interact with Gmail, Google Sheets, and all other Google Services.

The Google Script must be published as a public web app with anonymous access.

function doGet(e) {
  var result = {};
  try {
    if (e.parameter.origin === "digitalinspiration.com") {
      result = {
        "Email Aliases": GmailApp.getAliases().join(),
        "Drive Storage": DriveApp.getStorageUsed(),
        "Script Owner": Session.getActiveUser().getEmail()        
      };
    }
  } catch (f) {
    result.error = f.toString();
  }
  return ContentService     
  .createTextOutput(e.parameters.callback + '(' + JSON.stringify(result) + ')')
  .setMimeType(ContentService.MimeType.JAVASCRIPT);
}

The client slide JavaScript includes the jQuery library for making an HTTP GET request to the Google script. The webappUrl is the URL of the published Google Script web app and it begins with script.google.com


$.ajax({
  url: webappUrl,
  type : "GET", 
  data : {
    origin : 'digitalinspiration.com',
    input : JSON.stringify({
      color: "orange",
      country: "india"
    }) 
  },
  dataType: 'jsonp',
  success : function (data, status, xhr) {
    console.log("success");
    console.log(data);
  },
  complete : function (xhr, status) {
    console.log("complete");
  }
});
Categories
Code

How to Upload Files with UploadCare JavaScript API

UploadCare, like FileStack, lets users upload files to the cloud from their local computer or they can pull existing files from online cloud storage services like Google Drive, Dropbox, OneDrive and more. UploadCare offers a simple JavaScript based widget so you can add file uploading capabilities to any web page, including file upload forms and email builders, with a few lines of code.

This example shows how to add the UploadCare file upload widget to a web page and immediate preview the content of the file. The widget also shrinks the large images so they take less storage space in your account. The uploaded files are served via UploadCare CDN.


The system-dialog data attribute can be set to true if you would like to use the native system upload dialog. Please note that users will not be able to select files from cloud services when the native dialog is presented. Only images-only can be set to false to allow users to upload files of any MIME type and not just image files.

JavaScript file (requires jQuery)




Categories
Code

Delete Blank Rows from Tables in a Google Document with Apps Script

This Google App Script snippet will help you quickly delete all the blank rows of any tables inside your Google Document.

You can either pass the Google Drive File ID to the removeBlankRows() method or it will take the currently active Google Document as input.


/* 
*  Written by Amit Agarwal
*  Email: amit@labnol.org
*
*/

function removeBlankRows(docId) {

    var document = docId ?
        DocumentApp.openById(docId) :
        DocumentApp.getActiveDocument();

    var body = document.getBody();
    var search = null;
    var tables = [];

    // Extract all the tables inside the Google Document
    while (search = body.findElement(DocumentApp.ElementType.TABLE, search)) {
        tables.push(search.getElement().asTable());
    }

    tables.forEach(function (table) {
        var rows = table.getNumRows();
        // Iterate through each row of the table
        for (var r = rows - 1; r >= 0; r--) {
            // If the table row contains no text, delete it
            if (table.getRow(r).getText().replace(/\s/g, "") === "") {
                table.removeRow(r);
            }
        }
    });

    document.saveAndClose();
}
Categories
Code

Build an Image Uploader with Imgur API and JavaScript

If you are building a file uploader app that would allow users to upload images from the local disk to the web, Imgur is a good platform to start with. FileStack, Cloudinary and UploadCare are some popular web services that offer simple file upload widgets but the Imgur API is free for non-commercial usage or if your app is open source.

Go to api.imgur.com, register your application and generate the client ID. All HTTP requests for uploading images to Imgur must include the client_id in the authorization header and this would also let you upload images anonymously without the image being tied to your personal Imgur account.

In the HTML section of your website, include an <input> field of type file and set the accept attribute to “image/*” so that file selector window would only allow selection of image files. We’ll also add a data attribute (max-size) to reject files that are bigger than a specific size (in Kb).

Next, we use jQuery to attach an onChange event handler to the input field that gets triggered when the user clicks the input field and selects a file.

$("document").ready(function() {

  $('input[type=file]').on("change", function() {

    var $files = $(this).get(0).files;

    if ($files.length) {

      // Reject big files
      if ($files[0].size > $(this).data("max-size") * 1024) {
        console.log("Please select a smaller file");
        return false;
      }

      // Begin file upload
      console.log("Uploading file to Imgur..");

      // Replace ctrlq with your own API key
      var apiUrl = 'https://api.imgur.com/3/image';
      var apiKey = 'ctrlq';

      var settings = {
        async: false,
        crossDomain: true,
        processData: false,
        contentType: false,
        type: 'POST',
        url: apiUrl,
        headers: {
          Authorization: 'Client-ID ' + apiKey,
          Accept: 'application/json'
        },
        mimeType: 'multipart/form-data'
      };

      var formData = new FormData();
      formData.append("image", $files[0]);
      settings.data = formData;

      // Response contains stringified JSON
      // Image URL available at response.data.link
      $.ajax(settings).done(function(response) {
        console.log(response);
      });

    }
  });
});

The onChange handler makes as synchronous AJAX file upload request to the Imgur API with the image file sent inside the FormData object.

The form’s encoding type is set to multipart/form-data and thus the sent data is in the same format as the form’s submit method.

After the image is upload, Imgur returns a JSON response containing the public URL of the uploaded image and the deletehash that can be used to delete the file from the Imgur servers.

Categories
Code

Search Books with Goodreads API and Google Apps Script

The Goodreads API helps you query the entire database of books on the Goodreads website. You can find the ratings of books, fetch book reviews, search books by author or even publish your own reviews. This example shows how to connect to the GoodReads website through Google Apps Script, find books by title, parse the XML results as JSON and write the results in a Google Spreadsheet.

You can also extend the code to insert the thumbnail of the book image in a spreadsheet cell using the IMAGE function.

To get started, go to the Goodreads.com account and create a key. All Rest API methods will require you to register for a developer key.

Goodreads will return the response in XML format (see below) and we can use the XML service of Google Apps Script to easily parse this XML response.

Here’s the complete example. Remember to replace the API key with your own.


function GoodReads() {
  var search = "Taj Mahal";
  var books = searchBooks_(search);  

  // Write Data to Google Spreadsheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  books.forEach(function(book) {
    sheet.appendRow([book.title, book.author, book.rating, book.url]);
  });
}

function searchBooks_(query) {
  
  var baseUrl = "https://www.goodreads.com/book/show/",
      apiUrl = "https://www.goodreads.com/search/index.xml",
      apiKey = "ctrlq.org",
      searchResults = [],
      payload = {
        q: query,
        key: apiKey
      },
      params = {
        method: "GET",
        payload: payload,
        muteHttpExceptions: true
      };
  
  var response = UrlFetchApp.fetch(apiUrl, params);
  
  // API Connection Successful
  if (response.getResponseCode() === 200) {
    
    // Parse XML Response
    var xml = XmlService.parse(response.getContentText());
    var results = xml.getRootElement().getChildren('search')[0];
    
    // Save the result in JSON format
    results.getChild('results').getChildren().forEach(function(result) {
      result.getChildren('best_book').forEach(function(book) {
        searchResults.push({
          title: book.getChild('title').getText(),
          author: book.getChild('author').getChild('name').getText(),
          thumbnail: book.getChild('image_url').getText(),
          rating: result.getChild("average_rating").getText(),
          url: baseUrl + result.getChild("id").getText()
        });
      });
    });
    
  }

  return searchResults;
  
}
Categories
Code

How to List all your Team Drives in Google Drive with Apps Script

Google Team Drives are shared spaces in your Google Drive where you can store files and they instantly become available to all members of the Team Drive.

Unlike regular folders in Google Drive where the files are owned by the individual, files in Team Drive belong to the team and, if a user is no longer part of Team Drive, their files continue to be accessible.

While Google Team Drives are only available in the business and enterprise editions of G Suite, anyone, including consumer Gmail accounts and legacy Google Apps accounts, can be invited to become members of an existing Team Drive.

This Google Apps Script snippet uses the Google Drive API (v3) to determine the list of all Team Drives that the current user is a member of.

function getGoogleTeamDrives() {
  
  try {
    
    var teamDrives = {},
        baseUrl = "https://www.googleapis.com/drive/v3/teamdrives",
        token = ScriptApp.getOAuthToken(),
        params = {
          pageSize: 10,
          fields: "nextPageToken,teamDrives(id,name)"
        };
    
    do {
      
      // Written by Amit Agarwal @labnol
      // Web: www.ctrlq.org

      var queryString = Object.keys(params).map(function(p) {
        return [encodeURIComponent(p), encodeURIComponent(params[p])].join("=");
      }).join("&");
      
      var apiUrl = baseUrl + "?" + queryString;
      
      var response = JSON.parse(
        UrlFetchApp.fetch( apiUrl, {
          method: "GET",
          headers: {"Authorization": "Bearer " + token}
        }).getContentText());
      
      response.teamDrives.forEach(function(teamDrive) {
        teamDrives[teamDrive.id] = teamDrive.name;
      })
      
      params.pageToken = response.nextPageToken;
      
    } while (params.pageToken);
    
    return teamDrives;
    
  } catch (f) {
    
    Logger.log(f.toString());
    
  }
  
  return false;
  
}

The return object includes the ID of the Team Drive which is also the ID of the top level folder for this Team Drive. You can use the existingDriveApp service of Google Apps Script to create sub-folder or add new files to this folder.

Categories
Code

Send Data to Google Analytics with Measurement Protocol and Google Apps Script

This example shows how to send data to Google Analytics using the Measurement protocol. The event data is sent via POST because it allows for a larger payload. The parameter z is set with a random number for cache busting. We can set the hit type (t) to either pageview, event or exception.

function GoogleAnalytics_(t, param1, param2) {

    // Credit: @guimspace
    try {
        var meta = [];

        meta.push(
            ['v', '1'], 
            ['tid', 'UA-XXXXXXXX-1'], 
            ['cid', uuid_()], 
            ['z', Math.floor(Math.random() * 10E7)], 
            ['t', t]
        );

        if (t == 'event') {
            meta.push(['ec', param1], ['ea', param2]);
        } else if (t == 'exception') {
            meta.push(['dt', param1], ['exd', param2]);
        } else throw 101;

        var payload = meta.map(function (el) {
            return el.join('=');
        }).join('&');

        var options = {
            'method': 'post',
            'payload': payload
        };

        UrlFetchApp.fetch('https://ssl.google-analytics.com/collect', options);
        
    } catch (e) {}

    return;
}

/* Generates a random UUID to anonymously identify the client */
function uuid_() {
    return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function (c) {
        var r = Math.random() * 16 | 0,
            v = c == 'x' ? r : (r & 0x3 | 0x8);
        return v.toString(16);
    });
}
Categories
Code

Post an Update to Twitter with Google Apps Script

You can use Google Apps Script to post status updates (tweets) to Twitter. This example generates an OAuth 1.0a HMAC-SHA1 signature that is converted to base64 and is passed to the Twitter API via an HTTP POST request. You can use it to send tweets from Google Addons, Google Sheets and standalone web apps.


/* 
* Post to Twitter from Google Apps Script 
* Code by @rcknr
*/

function postTweet(message) {

  var method = "POST";
  var baseUrl = "https://api.twitter.com/1.1/statuses/update.json";
  var props = PropertiesService.getScriptProperties();

  var oauthParameters = {
    oauth_consumer_key: props.getProperty('CONSUMER_KEY'),
    oauth_token: props.getProperty('ACCESS_TOKEN'),
    oauth_timestamp: (Math.floor((new Date()).getTime() / 1000)).toString(),
    oauth_signature_method: "HMAC-SHA1",
    oauth_version: "1.0"
  };

  oauthParameters.oauth_nonce = oauthParameters.oauth_timestamp + Math.floor(Math.random() * 100000000);

  var payload = {
    status: message
  };

  var queryKeys = Object.keys(oauthParameters).concat(Object.keys(payload)).sort();

  var baseString = queryKeys.reduce(function(acc, key, idx) {
    if (idx) acc += encodeURIComponent("&");
    if (oauthParameters.hasOwnProperty(key))
      acc += encode(key + "=" + oauthParameters[key]);
    else if (payload.hasOwnProperty(key))
      acc += encode(key + "=" + encode(payload[key]));
    return acc;
  }, method.toUpperCase() + '&' + encode(baseUrl) + '&');

  oauthParameters.oauth_signature = Utilities.base64Encode(
    Utilities.computeHmacSignature(
      Utilities.MacAlgorithm.HMAC_SHA_1,
      baseString,
      props.getProperty('CONSUMER_SECRET') + '&' + props.getProperty('ACCESS_SECRET')
    )
  );

  var options = {
    method: method,
    headers: {
      authorization: "OAuth " + Object.keys(oauthParameters).sort().reduce(function(acc, key) {
        acc.push(key + '="' + encode(oauthParameters[key]) + '"');
        return acc;
      }, []).join(', ')
    },
    payload: Object.keys(payload).reduce(function(acc, key) {
      acc.push(key + '=' + encode(payload[key]));
      return acc;
    }, []).join('&'),
    muteHttpExceptions: true
  }

  var response = UrlFetchApp.fetch(baseUrl, options);
  var responseHeader = response.getHeaders();
  var responseText = response.getContentText();
  Logger.log(responseText);
}

function encode(string) {
  return encodeURIComponent(string)
    .replace('!', '%21')
    .replace('*', '%2A')
    .replace('(', '%28')
    .replace(')', '%29')
    .replace("'", '%27');
} 
Categories
Code

How to Update Multiple Cell Values in Google Sheets with Apps Script

The SpreadsheetApp service of Google App Script offers the range.setValues() method to update a single cell or a range of cells inside a Google Spreadsheet. You cannot, however, write data to multiple non-consecutive cells in a Spreadsheet using the setValues() method.

The Google Spreadsheet API, available inside Apps Script project through Advanced Google Services, can update multiple cells in one execution. You can write values in single cells, rows, columns or even a 2d matrix of cells.

function updateGoogleSheet(spreadsheetId) {

  /* Written by Amit Agarwal */
  /* Web: ctrlq.org  Email: amit@labnol.org */
  
  var data = [
    { 
      range: "Sheet1!A1",   // Update single cell
      values: [
        ["A1"]
      ]
    },
    {
      range: "Sheet1!B1:B3", // Update a column
      values: [
        ["B1"],["B2"],["B3"]
      ]
    },
    {
      range: "Sheet1!C1:E1", // Update a row
      values: [
        ["C1","D1","E1"]
      ]
    },
    {
      range: "Sheet1!F1:H2", // Update a 2d range
      values: [
        ["F1", "F2"],
        ["H1", "H2"]
      ]
    }];
  
  var resource = {
    valueInputOption: "USER_ENTERED",
    data: data
  };
  
  Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId);
  
}
Categories
Code

Find the Visitor’s Browser and OS with Google Apps Script

The HTML Service of Google Apps Script lets you create HTML and JavaScript based applications that can be served as standalone web apps or they can be used for building user interfaces (like a form) contained inside Google Sheets and Google Docs.

HTML service executes JavaScripts on the client side and communicates with server side Google Apps Script functions using google.script.run, an asynchronous client-side JavaScript API.

The File Upload Forms for Google Drive are built entirely with HTML service. The user visits an online web form, uploads a file to Google Drive and the form response is saved to Google Sheet. In addition to capturing user-entered data, we can also use Html Service to capture information about the visitor’s browser, their OS (Mac, Windows, etc) and their device (whether they are accessing the page from a tablet, mobile or a TV).

Here’s a sample snippet that shows how to parse the User Agent string using the ua-parser-js library. This method, however, cannot be used to find the I.P. Address of the visitor.

The server side Google Script.

function doGet() {
return HtmlService.createTemplateFromFile("index")
.evaluate()
.setTitle("User Agent - Google Apps Script");
}

And the index.html file served via HTML service.



  
    
  
  
    
Categories
Code

How to Convert Microsoft Excel to Google Spreadsheet Format with Apps Script

If your colleagues have been emailing you Microsoft Excel spreadsheets in xls or xlsx format, here’s a little snippet that will help you convert those Excel sheets into native Google Spreadsheet format using the Advanced Drive API service of Google Apps Script.

function convertExceltoGoogleSpreadsheet(fileName) {
  
  try {
    
    // Written by Amit Agarwal
    // www.ctrlq.org

    fileName = fileName || "microsoft-excel.xlsx";
    
    var excelFile = DriveApp.getFilesByName(fileName).next();
    var fileId = excelFile.getId();
    var folderId = Drive.Files.get(fileId).parents[0].id;  
    var blob = excelFile.getBlob();
    var resource = {
      title: excelFile.getName(),
      mimeType: MimeType.GOOGLE_SHEETS,
      parents: [{id: folderId}],
    };
    
    Drive.Files.insert(resource, blob);
    
  } catch (f) {
    Logger.log(f.toString());
  }
  
}

The script finds the existing Excel workbook by name in your Google Drive, gets the blob of the file and creates a new file of Google Sheets mimetype (application/vnd.google-apps.spreadsheet) with the blob.

You do need to enable the Google Drive API under Resources > Advanced Google Services and also enable the Drive API inside the Google Cloud Platform project associated with your Google Apps Script.

The other option, instead of specifying the mimetype, is to set the argument convert to true and it will automatically convert the source file into corresponding native Google Format at the time of insert it into Google drive.

function convertExceltoGoogleSpreadsheet2(fileName) {
  
  try {
    
    fileName = fileName || "microsoft-excel.xlsx";
    
    var excelFile = DriveApp.getFilesByName(fileName).next();
    var fileId = excelFile.getId();
    var folderId = Drive.Files.get(fileId).parents[0].id;  
    var blob = excelFile.getBlob();
    var resource = {
      title: excelFile.getName().replace(/.xlsx?/, ""),
      key: fileId
    };
    Drive.Files.insert(resource, blob, {
      convert: true
    });
    
  } catch (f) {
    Logger.log(f.toString());
  }
  
}
Categories
Code

Google File Picker API with Google Drive – Working Example

The Google File Picker API lets users easily upload files to Google Drive and also select existing files and folders from Drive. The File Upload Forms for Google Drive is written in Google Apps Script and it lets users upload files to the form owner’s folder through the File Picker API.

Here’s a complete working example that shows how to integrate the File Picker API with a Google Apps Script based web app. You’d need to enable the Google Picker API from your Google Console and also generate the developer’s key.


// Server.gs
function doGet() {    
  return HtmlService
  .createTemplateFromFile("picker")
  .evaluate()
  .addMetaTag("viewport", "width=device-width, initial-scale=1")
  .setTitle("Google Drive Picker")
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);  
}

function include(fileName) {
  return HtmlService
  .createHtmlOutputFromFile(fileName)
  .getContent();
}

function initPicker() {
  return {
    locale: 'en',
    token: ScriptApp.getOAuthToken(),
    origin: "https://script.google.com",
    parentFolder: "xyz",
    developerKey: "ctrlq.org",
    dialogDimensions: {
      width: 600, 
      height: 425
    },
    picker: {
      viewMode: "LIST",
      mineOnly: true,
      mimeTypes: "image/png,image/jpeg,image/jpg",
      multiselectEnabled: true,
      allowFolderSelect: true,
      navhidden: true,
      hideTitle: true,
      includeFolders: true,
    }
  };
}

// For Scope
// DriveApp.getStorageUsed()
// DriveApp.getFilesByName("ctrlq.org")
// picker.html
// Offers button for uploading and selecting files


  
    
    
    
        
  
  
    

// javascript.html

Categories
Code

List All Users of a Google Apps Domain in Google Spreadsheets

The enterprise editions of Google Drive Auditor and Gmail Address Extractor use the Google Apps Admin SDK (Directory API) with Google Apps Script to create a list of all users that are part of a G Suite (Google Apps) domain.

The Google Scripts gets the name and email address of users in the organization and saves the list inside a Google Spreadsheet. This script can only be executed by the domain administrator.

function getDomainUsersList() {
  
  var users = [];
  var options = {
    domain: "ctrlq.org",     // Google Apps domain name
    customer: "my_customer",
    maxResults: 100,
    projection: "basic",      // Fetch basic details of users
    viewType: "domain_public",
    orderBy: "email"          // Sort results by users
  }
  
  do {
    var response = AdminDirectory.Users.list(options);
    response.users.forEach(function(user) {
      users.push([user.name.fullName, user.primaryEmail]);
    });
    
    // For domains with many users, the results are paged
    if (response.nextPageToken) {
      options.pageToken = response.nextPageToken;
    }
  } while (response.nextPageToken);
  
  // Insert data in a spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Users") || ss.insertSheet("Users", 1);
  sheet.getRange(1,1,users.length, users[0].length).setValues(users);
  
}

Remember to replace ctrlq.org with your own domain address. You will need to enable the Admin Directory API under Resources > Advanced Google Services.

Then go to Resources > Cloud Platform Project, click the Project name to open the Google Developer console associated with your Apps Script project. Switch to the Library section, search for Admin SDK and enable the API.

Categories
Code

Using the Google Slides API with Apps Script for Document Merge

Google Slides API lets you programmatically create new presentations and edit existing ones. You can pull data from an external data source, like a Google Spreadsheet or Salesforce CRM, and create a nice-looking report in the form of a presentation. You can convert a Google Document into Google Slides or export a presentation as a PDF file.

You can also read an existing presentation using the API and modify individual elements on slides. For instance, if your organization logo has changed, you can use the Google Slides API to update the embedded images inside all slide via the API. Or if you would like to delete all slide that contains specific text, that can be done with the Google Slides API.

Replace Text in Google Slides presentation

You can use Google Apps Script to modify your Google Slides presentation. This quick example shows how to replace the markers in a presentation with actual text. Do enable the Slides API under Advanced Services.


function mergeGoogleSlide() {  
  
  try {
    
    var presentationId = "PRESENTATION_ID";
    
    var requests = [{
      'replaceAllText': {
        'containsText': {'text': "<>"},
        'replaceText': "Amit Agarwal"
      }
    },{
      'replaceAllText': {
        'containsText': {'text': "<>"},
        'replaceText': "amit@labnol.org"
      }
    }];
    
    Slides.Presentations.batchUpdate({'requests': requests}, presentationId);
    
  } catch (e) {
    Logger.log(e.toString());
  }
}

In the next example, we directly use the REST Google API to access the slides inside Google Apps Script.


// Fetch all Tables and Shape elements inside the presentation
function googleSlidesAPI(presentationId) {
  
  var base = "https://slides.googleapis.com/v1beta1/presentations/";
  var apiUrl = base + presentationId + "/pages/pageId?fields=pageElements(table,shape)";
  
  var params = {
    method:"get",
    contentType: "application/json",
    headers: {
      "Authorization": "Bearer " + ScriptApp.getOAuthToken()
    },
    muteHttpExceptions:true                
  };
  
  // returns a JSON response
  var resp = UrlFetchApp.fetch(apiUrl, params);
  
  Logger.log(resp.getContentText());

}

Similarly, you can make HTTP POST requests to insert text boxes or images, for replacing text or for deleting specific page elements from slides. To replace text everywhere within a presentation, use a ReplaceAllTextRequest request.

Categories
Code

Send SMS with Google Forms

A teacher has created a Google Form where students enter their name, the course they’ve enrolled into and their parent’s mobile number. The requirement is that as soon as a student enrolls in a course, an automated SMS message should be sent to the parent’s cell phone.

This can be easily done with OnFormSubmit trigger in Google Apps Script and SMS API. We are using MSG91 for this example but you can use Twilio or any SMS service that offers an HTTP Rest API for sending text messages.

function createTriggers() {
  
  ScriptApp
  .getProjectTriggers()
  .forEach(function(trigger) {
    ScriptApp.deleteTrigger(trigger);
  });
  
  ScriptApp
  .newTrigger("triggerSMS")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit()
  .create();
  
}

function triggerSMS(e) {

  var response = e.namedValues;
  var message = response["Student Name"].toString() + " enrolled for " + response["Course"].toString();  
  var url="https://control.msg91.com/api/sendhttp.php";
  
  var payload = {
    "authkey" : "AUTH_KEY",
    "mobiles" : response["Mobile Number"].toString(),
    "message" : encodeURIComponent(message),
    "sender"  : "GFORMS",
    "route"   : 4
  };
  
  var options = {
    "method"  : "POST",
    "payload" : payload,   
    "followRedirects" : true,
    "muteHttpExceptions": true
  };
  
  UrlFetchApp.fetch(url, options);
  
}
Categories
Code

Differences between Google Team Drive and My Drive

G Suite Enterprise and G Suite Business users get unlimited storage and they can create Team Drives inside Google Drive. Unlike files in My Drive, files in Team Drive belong to the team instead of an individual.

This table outlines the differences between My Drive and Team Drives in Google Drive. It is currently not possible to sync files stored in Team Drives with your Mac or PC using the Google Drive client.

Google Team Drives My Drive
Who owns files and folders? The team The individual who created the file or folder
Can I restore files? Yes, if you have edit access or full access. Yes, if you created it.
Can I move files? To move files between Team Drives or from My Drive into a Team Drive, drag the files into the destination Team Drive folder. When you move a folder from My Drive to Team Drive, the ID will change. To move files between Team Drives, you must have full access to the original Team Drive and at least edit access to the destination Team Drive. Yes
Can I move folders? Only the domain admin can move folders inside Team Drives. Yes
Sharing All team members see the same file set. Different users might see different files in a folder, depending on their
access to individual files.
How long do files I delete stay in Trash? Each Team Drive has its own Trash. Only members with full access to the Team Drive can delete files. Files and folders in Trash are deleted permanently after 30 days or sooner if a full-access member deletes them first. Full access and edit access members can restore content from Trash. If you own the file you delete, it’s automatically removed from Drive on all of your devices and on the web. Deleted files are moved to Trash. They still count toward your total storage until you permanently delete them from Trash. If you delete a file that you don’t own or that’s been shared with you, other people can still access it until the owner deletes it.
Categories
Code

Load External JavaScript Libraries in Google Scripts with eval()

You can include external JavaScript libraries or any other script in your Google Apps Script projects in multiple ways.

The best option would be create a new script (.gs) file inside your Google Script and copy-paste the entire JavaScript code into the file. This makes it easy for you to debug the code right inside the Apps Script IDE.

Alternatively, you can create a new HTML file inside apps script, copy-paste the code inside that file and use the eval() method as shown here:

 function loadJSFromHTMLFile() {
  var javascript = HtmlService
       .createTemplateFromFile("script.html").getRawContent();
  eval(javascript);
 }

If the JavaScript file is on a remote server or your Google Drive, you can use the UrlFetchApp and Drive.getFileById() method respectively to import the script into your Google Script at execution time.

// Load JavaScript from External Server
function loadJSFromServer() {
  var url = "https://example.com/script.text";
  var javascript = UrlFetchApp.fetch(url).getContentText();
  eval(javascript);
}

// Load JavaScript from Google Drive
function loadJSFromGoogleDrive() {
  var rawJS = DriveApp.getFileById(id).getBlob().getDataAsString();
  eval(rawJS);
}

Finally, if you need to load multiple JavaScript libraries from a remote CDN, this technique by @BriaEgan will be useful. It creates the variables in the global namespace.


// Credit Brian @github
var LIBRARIES = {
  prettyDate:  "http://ejohn.org/files/pretty.js",
  underScore: "http://underscorejs.org/underscore-min.js",
}

Object.keys(LIBRARIES).forEach(function(library) {
  newFunc = loadJSFromUrl(LIBRARIES[library]);
  eval('var ' + library + ' = ' + newFunc);  
});

function loadJSFromUrl(url) {
  return eval(UrlFetchApp.fetch(url).getContentText());
}
Categories
Code

Google OAuth 2.0 Service account with PHP and Google APIs

This sample code uses Google Service Accounts with PHP to get a list of users in a Google Apps Domain using the Admin Directory API.

To get started, install the Google API PHP library, create a Google Service account with domain-wide delegation and save the private JSON file in the same directory as your PHP application.

useApplicationDefaultCredentials(); 
  $client->setApplicationName(APPLICATION_NAME);
  $client->setScopes(SCOPES); 
  $client->setAccessType("offline");
  $client->setSubject($user);

  $service = new Google_Service_Directory($client); 

  $optParams = array( 
    'domain' => 'ctrlq.org', 
    'maxResults' => 10, 
    'orderBy' => 'email'
  ); 

  $results = $service->users->listUsers($optParams); 

  print_r($results);

?>
Categories
Code

How to Create Google Service Accounts

This step by step tutorial will walk you through the steps to create a Google service account using the Google developer console. We’ll also see how to enable Google APIs that our application will access via the service account credentials.

1. Go to console.developers.google.com and create a new project. Let’s call this Service Accounts Tutorial.

create-service-account.png

2. Go to the Library section and enable a couple of Google APIs and service that you will use in the project.

2017-01-17_12-05-24.096.png

3. Enable the Admin SDK. This will allow the Google Apps domain to manage the users in the domain.

2017-01-17_12-07-14.140.png

4. Go to the manu, choose IAM & Admin, Service Accounts and create a new service account.

Set the Role as Service Account Actor, choose JSON for the private key, enable G Suite Domain-wide delegation. This is essential since we would like the application to access the data of all user’s in the G Suite domain without manual authorization on their part.

service-account-domain-wide-delegation.png

The JSON file that contains the client credential will download to your computer. Store it in a safe location because you will not be able to download it again.

The service account has been successfully created. The JSON fill will the Private key and the Client Email that will use in our application. The file will also have the Client ID that will need to whitelist this application in the Google Apps admin console.

google-apps-service-account-private-key.png

In the next chapter, we’ll see how the G Suite domain administrator can setup the OAuth2 application inside admin console for domain wide delegation.

Categories
Code

Service Accounts and Google APIs – Introduction

When a user connects to an application that uses OAuth 2 authentication, they are presented with a consent screen that describes what information about their account will be shared with the application and it may also includes a list of various Google APIs that the application has requested access to.

Google’s authorization server provides an access token to the application that they can pass to Google with all future requests to authenticate the request.

However in some cases, you may want to build a server-side application that connects directly to Google services without the involvement of the end-user. That’s where Service Accounts come into the picture.

Service Accounts are pre-authorized meaning the user has already granted access to a service account to access Google services on their behalf. The application then uses the service account credentials to connects to Google APIs removing the user from the equation.

The service account acts sort of virtual user and they have an email address so you can share your Google Calendar, Google Drive folders and other resources with a service account. If you are building a web app that uses Google Drive APIs for converting documents from one format to another, service accounts may be an option as the user would not be required to grant access to their own Google Drive for converting files.

Service Accounts with OAuth also support user impersonation and this is particularly useful for Google Apps admins that can build apps to access data of any user in the Google Apps domain. For instance, the Google Apps admin can use service accounts to audit shared files of all users in the organization.

In the next section, we’ll look at the step to create a service account inside the Google Developer console.

Categories
Code

Configure OAuth2 Service Accounts for Domain-wide Delegation

The tutorial describes how the Google Apps domain of a G Suite domain can configure an OAuth2 Service account application for domain wide delegation. That is, the service account user can act on behalf of any other user of the Google Apps domain.

1. Go to admin.google.com and sign in to the G Suite administrative console.

2. Click the Security icon, choose API reference and check the option Enable API access. This will allow the admin programatic access to various G Suite Administrative APIs.

api-access-admin-console.png

3. On the Security Page, click Show More and then choose Advanced Settings. In the Advanced Setting section, click Manage API access. The domain admins can use this section to control access to user data by applications that use OAuth protocol.

2017-01-17_12-36-51.299.png

4. You can now authorize whitelisted application to access the data of the domain users without them having to individually give consent or their passwords. Also, you need to specify a list of OAuth 2.0 API scopes (comma separated) that the authorized API client is allowed access to on user’s behalf.

You can get the Client ID from the JSON file while the API scopes are the all the APIs that we have enabled while creating the Google Service Account.

api-client-access.png

For instance, if your application needs access to user’s Gmail, Google Drive and Admin SDK, the API scopes would be:

https://www.googleapis.com/auth/admin.directory.user.readonly, https://mail.google.com, https://www.googleapis.com/auth/drive

The service account is now ready and the application is authorized in the Google Apps admin console. In the next step, we’ll look a building an OAuth2 application that uses Google Service Accounts with Google Apps Script.

Categories
Code

Using Google Service Accounts with Google Apps Script

This sample code shows how to use OAuth in Google Apps Script using Service Accounts. The G Suite admin can access the Google Drive files of any user – the username or email address of the user you are trying to impersonate specified with the method setSubject.

For this code to work, you need to create a Google Service account with domain-wide delegation, substitute the private key and client client email with the actual values and also add the Client Id to your Google Apps admin console with the Drive API Scope. The OAuth 2.0 access tokens are stored in the Script Properties.


var JSON = {
    "private_key": "Your Private Key",
    "client_email": "serviceacount@project-ctrlq.iam.gserviceaccount.com",
    "client_id": "1234567890",
    "user_email": "amit@labnol.org"
};

function getOAuthService(user) {
    return OAuth2.createService("Service Account")
        .setTokenUrl('https://accounts.google.com/o/oauth2/token')
        .setPrivateKey(JSON.private_key)
        .setIssuer(JSON.client_email)
        .setSubject(JSON.user_email)
        .setPropertyStore(PropertiesService.getScriptProperties())
        .setParam('access_type', 'offline')
        .setScope('https://www.googleapis.com/auth/drive');
}

function getUserFiles() {
    var service = getOAuthService();
    service.reset();
    if (service.hasAccess()) {
        var url = 'https://www.googleapis.com/drive/v2/files?pageSize=1';
        var response = UrlFetchApp.fetch(url, {
            headers: {
                Authorization: 'Bearer ' + service.getAccessToken()
            }
        });
        Logger.log(response.getContentText());
    }
}

function reset() {
    var service = getOAuthService();
    service.reset();
}

It is important to specify the user’s email on behalf of whom you wish to run this application else you’ll get a “Not Authorized to access this resource/api” error.

Also, if you are getting the 403 Insufficient permission error, it is likely because the application is request access to API scopes that are not authorized in the Google Apps admin console. The invalid_grant error is likely due to incorrect date and time settings of the server that is hosting the application.

Categories
Code

Tutorial: Create Application with Google APIs and OAuth 2

Now that you understand the basics of Google OAuth 2, let’s build a simple application that uses OAuth 2.0 for connecting to Google API’s on user’s behalf.

1. Go to console.developers.google.com and create a new project.

2017-01-16_22-23-56.311.png

2. Give your project a name.2017-01-16_22-25-24.046.png

3. Go to Library, search for the Google APIs that you wish to use in your application and enable them one by one.

enable-gmail-api.png

4. Go to Credentials -> Create Credentials -> Oauth Client Id. We’ll discuss service accounts in a later chapter.

oauth-client-id.png

5. On the credentials screen, choose “Web Application” for the “Application Type” and http://localhost:8080 under Authorized redirect URIs and Authorized JavaScript origins.

If you have a live web server running PHP, you can put the site address for the redirect URI but for this application, we will only use a local server running on port 8080. You only need to specify the port if your web service is running on a port than 80 (standard).

google-oauth-credentials.png

Google will show you the Client ID and Client Secret on the next screen. Dismiss the window and instead download the JSON file containing your OAuth client credentials. Move this JSON file in the root directory of your PHP application.

client-secret-json.png

In the next section, we’ll look at the actual PHP example that will connect our application to the user’s Gmail account via OAuth.

Categories
Code

An Introduction to OAuth 2 and Google APIs

google-apps-gsuite.png

OAuth 2, in simple English, is a simple protocol that makes it easy for third-party applications to access a user’s account without the user having to give out their username and password to the application. Let me explain with an example.

The Save Emails addon downloads email messages from a user’s Gmail account to their Google Drive. The addon is the third-party application in this case and it can use OAuth 2.0 to obtain permission from the user to access their Gmail and Google Drive via standard Google APIs. The user can revoke access to the addon anytime. Also, if the user changes the password of their Google account later, the OAuth 2 connection will continue to work.

OAuth 2.0 Flow and Google APIs

The OAuth 2.0 flow is something like this.

  1. You create a new project in the Google Console for your application and generate the client ID and client secret. These keys are known both to your application and Google.
  2. Enable the various Google APIs that your application would use to obtain data from the user’s account. For instance, if you are the admin of a Google Apps domain, you may want to activate the Admin Directory SDK to get a list of all users in the domain.
  3. The application then redirects the browser to a  URL on the Google server. Here the user needs to give consent on whether the application should be granted access to their data on not.
  4. If the user approves, the Google Authorization Server redirects the user back to your application with a single-use authorization code that can be exchanged for a short-lived access token and a refresh token.
  5. Store the tokens in Google Cloud Storage, Firebase, MySQL database or even the local filesystem for command line applications.
  6. All future requests to the Google APIs should include this access token. The access token expires after some time so your application can use the refresh token to obtain a new access token. The refresh tokens do not expire.

In the next section, we’ll build a simple web application that uses OAuth 2.0 with Gmail API and Google PHP library to generate a list of all labels in the user’s mailbox.

Build OAuth 2 Application with Google APIs

Categories
Code

Connect to Google API with PHP and OAuth2 – Sample Code

This sample application describes how your PHP application can connect to the user’s Gmail account using the Google PHP client library and OAuth2. You’ll need to create the application inside Google Console.

The Client ID and secret are stored in a separate JSON while the access token and refresh token are also stored in the local file system.

setApplicationName("ctrlq.org Application");
  $client->setAuthConfig($KEY_LOCATION);
  
  // Incremental authorization
  $client->setIncludeGrantedScopes(true);
  
  // Allow access to Google API when the user is not present. 
  $client->setAccessType('offline');
  $client->setRedirectUri($REDIRECT_URI);
  $client->setScopes($SCOPES);
  
  if (isset($_GET['code']) && !empty($_GET['code'])) {
      try {
          // Exchange the one-time authorization code for an access token
          $accessToken = $client->fetchAccessTokenWithAuthCode($_GET['code']);
          
          // Save the access token and refresh token in local filesystem
          file_put_contents($TOKEN_FILE, json_encode($accessToken));
          
          $_SESSION['accessToken'] = $accessToken;
          header('Location: ' . filter_var($REDIRECT_URI, FILTER_SANITIZE_URL));
          exit();
      }
      catch (\Google_Service_Exception $e) {
          print_r($e);
      }
  }
  
  if (!isset($_SESSION['accessToken'])) {
      
      $token = @file_get_contents($TOKEN_FILE);
      
      if ($token == null) {
          
          // Generate a URL to request access from Google's OAuth 2.0 server:
          $authUrl = $client->createAuthUrl();
          
          // Redirect the user to Google's OAuth server
          header('Location: ' . filter_var($authUrl, FILTER_SANITIZE_URL));
          exit();
          
      } else {
          
          $_SESSION['accessToken'] = json_decode($token, true);
          
      }
  }
  
  $client->setAccessToken($_SESSION['accessToken']);
  
  /* Refresh token when expired */
  if ($client->isAccessTokenExpired()) {
      // the new access token comes with a refresh token as well
      $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
      file_put_contents($TOKEN_FILE, json_encode($client->getAccessToken()));
  }
  
  $gmail = new Google_Service_Gmail($client);
  
  $opt_param               = array();
  $opt_param['maxResults'] = 10;
  
  $threads = $gmail->users_threads->listUsersThreads("amit@labnol.org", $opt_param);
  
  foreach ($threads as $thread) {
      print $thread->getId() . " - " . $thread->getSnippet() . '
'; } ?>
Categories
Docs

File Upload Forms – Getting Started Guide

This step by step tutorial will help you get started with File Upload Forms for Google Drive.

Design the Form

1. Go to forms.studio and design your form using the WYSIWYG editor. You can even embed images and videos in your Forms.

google forms studio

2. Save the form and click Preview Form Design to check the layout of the draft form. Copy the embed code from the Form Preview page on to your clipboard.

Configure the Google Spreadsheet

3. Open the Google Spreadsheet that was automatically created in your Google Drive with the premium license. The form responses will be stored in this sheet.

4. Go to Addons > File Upload Forms > Form Settings and authorize the File Upload Forms script. The script runs in your own Google Account and even a single byte of data never gets shared with anyone.

[imagemap]imagemap[/imagemap]

5. Update the form configuration and save the changes. (The above screenshot is interactive – you can hover the mouse to know more about any setting)

6. Inside the same Google Spreadsheet, go to Tools > Script Editor. Switch to the forms.html file and paste the HTML embed code. Save your changes.

google forms html code

Deploy the File Upload Form

7. While you are inside the Script Editor, go to Publish > Deploy as Web App.  Choose “Me” under “Execute the App as” and choose “Anyone, even anonymous” under “Who has access to the app.”

Click Deploy and make a note of the web app URL. That’s your File Upload Form page that you can share with the world.

deploy google form

For help, please refer to the File Upload Forms documentation. This guide is also available on Google Slides.

Categories
Docs

How to Add Images, Videos and Google Maps in File Upload Forms

This step by step guide explains how you can embed logos, images, videos, tweets, Google Maps, or any other web element in  your File Upload Forms.

To embed an image, go to this free image hosting service and upload the image from your computer. Copy the HTML embed code to the clipboard.

Next open your form inside Forms Studio and drag the <HTML> field into the canas.

html-form-field.png

Expand the HTML field and you can paste the embed code of the image here. Click Save Form and preview to test your image on a live form.

logo-upload.png

You can use the same technique to embed Google Maps, tweets, YouTube Videos or even Instagram pictures. These websites provide the embed code inside IFRAME tags that can be added to the HTML field of the Form.

 

Categories
Docs

How to Edit your File Upload Forms

This step by step guide explains how you can edit your File Upload Forms and add, edit or delete fields in the form. You may refer to a previous guide on how to edit colors of the form.

Step 1: Go to Forms Studio and sign-in with your Google Account. You’ll see a list of all the forms that you’ve created earlier. You can either create a new form or edit an existing form.

forms-studio.png

Step 2: After you’ve created the forms inside Form Studio, click the Save Form button followed by Preview Form Design to test the actual layout of your form.

preview-html-form.png

Step 3: On the Preview screen, scroll to the bottom of the page and copy the embed code to your clipboard.

clipboard.png

Step 4: Open the Google Sheet associated with your File Upload Form, go to Tools > Script Editor and click the forms.html file. Remove all the existing code and then paste the code you copied to the clipboard in the previous step. Save the changes.

html-form-code.png

Step 5: The forms have been updated but changes are not live yet. Go to Publish > Deploy as web app and choose a new Project Version. Then click Update and your update form will go live instantly.

deploy-web-app.gif

 

 

Categories
Docs

How to use Google Analytics with File Upload Forms

File Upload Forms for Google Drive can be integrated with Google Drive and you can easily track how many people have visited your page, what browser they user, where they came from and other useful information.

analytics-google-form.png

  1. Go to google.com/analytics, sign-in with your Google account and select the Admin tab.
  2. In the ACCOUNT column, use the dropdown menu to select the account to which you want to add the property.
  3. In the PROPERTY column, select Create new property from the dropdown menu.
  4. Select Website for Website or Mobile app, enter the Form Title for the website name and put https://script.google.com in the website URL field.
  5. Click Get Tracking ID that will be something like UA-XXXX-YY

Open the Google Spreadsheet associated with you web form, go to Addons > File Upload Form > Settings and add the tracking id in the GA code field (see screenshot).

Check your Analytics Real-Time reports

With the Real-Time reports, you can monitor user activity as it happens.

  1. Sign in to your Analytics account.
  2. Navigate to a view in the property to which you added the tracking code. If you only recently added the tracking code to this property (website), it is likely that there will only be one view.
  3. Select the Reporting tab.
  4. Select Real-Time > Overview.
Categories
Docs

How to Reorder Columns in File Upload Spreadsheet

When a new response is submitted via the File Upload Form, the data is saved in a Google Spreadsheet while the files are saved in your Google Drive. The first row is the header containing the question titles while the other rows are answers, one row per response.

Later, if you edit the form design and add, edit or update form fields, the header row of the Google Spreadsheet will be updated as well.

reorder-spreadsheet-columns.gif

However, as you may notice, the order of columns in the Google spreadsheet may not be the same as the order in your Google Form. If this is an issue, you can manually move the entire columns to the correct position in the spreadsheet.

When a new response is received, the data will automatically go into the right columns.

Categories
Docs

How to Restrict Access to your File Upload Forms

When you publish a new File Upload Form, it is public by default meaning anyone on the web can access your form and submit a response. However, there are few things to protect your Google Form from spam and also make is available only to select users.

1. Add a Password

Open the Spreadsheet, go to Addons > File Upload Forms > Settings and specify a Form Password. The default is NONE meaning the user need not enter a password but you can enter any password here and only users who know the password can fill your form.

2. Add a CAPTCHA

When design the form inside the online form builder, add a CAPTCHA field and your form will be protected from spam bots. The CAPTCHA is powered by Google reCAPTCHA and therefore pretty foolproof.

3. Restrict Form Access

When publishing the file upload form as a web app, you get a few options. Selection the option that works perfect for your case.

Inside the spreadsheet, go to Tools > Script Editor > Publish > Deploy As Web app. Here, under “Who has access”, choose any of the following option:

  • Anyone, even anonymous – Your form becomes public and anyone on the Internet can access and fill your form.
  • Anyone – Only users who are logged into their Gmail or Google Account can fill your file upload form.
  • Anyone, with xyz.com – If you are a Google Apps user, you can choose this option to restrict form access to users who are in your organization.

Troubleshooting:

If you do not see the anonymous option while publishing your file upload form, it is likely because of a setting in your Google Apps domain. The admin may have restricted users from sharing files outside the organization and thus you do not see that option.

Categories
Docs

How to Allow Multiple File Uploads in Web Forms

Say you are a branding agency and you require applicants to upload 3 photographs with their cover later. There are two options. You can either have 3 different file fields inside your File Upload Forms or you can have a single file field that allows users to upload multiple files.

Single File Upload

single-file-upload.png

Multiple File Uploads

multiple-file-uploads.png

The only disadvantage with multiple file upload fields is that a user can upload any number of files so it may be a better idea to have multiple file upload fields.

To allow multiple file uploads, go to Addons > File Upload Form > Form Settings and check the option “Allow Multiple Uploads”.

Categories
Docs

How to Embed File Upload Forms in your Website

You can easily embed File Upload Forms in any website be it a WordPress blog, a Squarespace site, a WooCommerce store, your company’s intranet or even Google Sites.

Your File Upload Form is internally a web app hosted on script.google.com. You can use IFRAME tags to embed this web app into any other page but before doing that, we need to make a few changes in the Form Settings.

This is required because the File Picker, the library that helps users upload files on to your Google Drive, is configured to work on a single web domain and doesn’t allow multiple origin domain.

What that means is if you have a form served from script.google.com and you change the embed domain to, say, example.com, the file upload will only work on example.com and not the script.google.com domain.

embed-form.png

How to Embed File Upload Forms

  1. Open the associated Google Sheet, go to Addons > File Upload Forms > Form Settings and put your website domain in the EMBED DOMAIN field. For instance, if you wish to embed the form on example.com, you need to put http://example.com in the field.
  2. Save the settings and close the window.
  3. Go to Tools > Script Editor and choose Publish > Deploy as Web App and make a note of the web app URL (see screenshot above).

The web app URL has this format:

https://script.google.com/macros/abc/exec

The embed code would be something like this:

Remember to replace the URL in the IFRAME code with the URL of your web app. You can also change the height and width value as per the design of your own website.

Categories
Docs

How to Restrict File Uploads to Certain Type of Files

With File Upload Forms, you can easily build web forms that would let anyone upload files to your Google Drive. The files are uploaded via the Google File Picker and sent straight to a folder in your Google Drive.

File upload fields, by default, would allow users to upload files of any extension. However, if you wish to restrict users to only upload files of selected types, that can be easily configured through form settings.

Go to the Google Sheet, Addons, File Upload Forms, Form Settings and here look for the field Allowed File Types. The default value is ANY meaning all file types are accepted.

Valid file types are IMAGE, AUDIO, VIDEO, PDF, WORD, POWERPOINT, EXCEL, ZIP, TEXT or ANY for all types.

You can also specify multiple file types in the field. For instance, IMAGE,AUDIO,VIDEO would allow the user to only upload images and media files while WORD,PDF would restrict users to PDF and Microsoft Word documents.

Internally, the File Picker looks at the MIME Type of the file as shown in this table.

File Type Valid MIME Types
IMAGE image/jpeg,image/jpg,image/gif,image/png,image/bmp
VIDEO video/x-flv,video/mp4,video/quicktime,video/x-msvideo,video/x-ms-wmv,video/ogg,video/webm,video/3gpp
AUDIO audio/aac,audio/mp4,audio/mpeg,audio/ogg,audio/wav,audio/webm,audio/mp3,audio/3gpp
PDF application/pdf
WORD application/vnd.ms-word,application/msword,application/vnd.openxmlformats-officedocument.wordprocessingml.document
POWERPOINT application/vnd.ms-powerpoint,application/vnd.openxmlformats-officedocument.presentationml.presentation
EXCEL application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
TEXT text/css,text/html,text/plain,text/tab
ZIP application/x-gzip,application/x-bzip2,application/x-tar,application/x-tar,application/zip,application/x-compressed-zip
Categories
Docs

How to Send Email Notifications with File Upload Forms

File Upload Forms for Google Drive can send email notifications when a new responses is submitted. The email message can be sent to any email address, including the form submitter’s email.

file-upload-forms.png

Open the Google Sheet associated with the Form and go to File Upload Forms > Form Settings (screenshot). To enable notifications, check the option “Send Email Notications” and fill the input fields for Email Address, Email Message and Email Body.

You can specify one or more email addresses in the Email Address field, separated by comma. Also, if you wish to send the notification to the email address of the form submitter, you can put {{ Email Address}} in the field where “Email Address” is the title of the question that asks for the user’s email address.

You can also include any form field in the message subject and message body by enclosing the field inside curly double braces.

For instance, if the title of your question is “What is your name?”, put the Hello {{What is your name?}} in the email subject and Martin fills the form, they’ll see Hello Martin in the email subject.

The email body will include all the form fields by default and also the uploaded file will be added to the email as file attachments.

Categories
Docs

How to Change the Colors of File Upload Forms

File Upload Forms use the Materialize CSS framework that conforms to Google’s Material Design. The color palette is based on the material design base colors. The form layout is also responsive meaning they work on both desktop and mobile devices.

file-upload-colors.png

If you would like to change the default color theme of your web form, you would need to update the CSS of your form. To get started, open the Google Sheet associated with your File Upload form, go to Tools > Script and switch to the css.html file.

Here, let’s look at body class which has background color set to #e0f2f1 (a shade of green). You can replace this value with any other hex color to change the background color of your web form.

The form structure has the body, the upper section (ctrlqHeaderMast), the accent line (ctrlqAccent) and the actual form box (ctrlqFormContent).  The submit button is .btn-large.

You can apply the styles to the corresponding class or HTML element to format it. Here are some CSS styles that will change the color theme to dark gray.

body {
 background: #455A64;
}
.ctrlqHeaderMast {
  background: #607D8B;
}
.ctrlqAccent {
  background: ##CFD8DC;
}
.btn, btn-large {
 background: #455A64;
}
.btn:hover, .btn-large:hover{
 background: #607D8B;
}

After you have updated the colors inside css.html, save the file and then go to Publish > Deploy as Web App and publish a new version of the app.

Please remember that the new colors would only be applied after you publish a new version of the web app.

See Google’s own color palette for recommended set of colors of material design.

color-pallete.png

Categories
Docs

How to Add Data Validation in File Upload Forms

When building the form with Forms Studio, you can easily add data validation rules using regex and that will allow to you automatically validate the user’s input. The user will be notified though inline error message of invalid input data before submitting a form.

If you are new here, it would help if you can get a basic understanding of Regular Expressions. They can also be used in Google Docs, Microsoft Word, Google Forms and most text editors.

To get started, sign in to Google Forms Studio with your Gmail account and create a new form or manage an existing form. Here expand any input field and put the data validation rule under the Regex Pattern field.

data-validation-regex.png

For instance, the regex [0-9]{5}(-[0-9]{4})? will match U.S. zip code consisting of five numbers with an optional hyphen and four-digit add-on.

The regex [a-zA-Z0-9]{5,10} will match any set of alpha numeric characters that are between 5 to 10 characters in length.

For exact matches, you can enclose the regular expression in the caret (^) and dollar sign ($) markers. A ^ matches the position at the beginning of the input string and a $ matches the position at the end of the string. If any characters are entered other than the required match, the input would be rejected.

^\d{3}-\d{2}-\d{4}$ will only match social security numbers.

^4[0-9]{12}(?:[0-9]{3})?$ will match all Visa card numbers as they start with a 4 and can have either 16 or 13 digits.

^\d{3}[-.]?\d{3}[-.]?\d{4}$ will match US phone numbers.

^[A-Za-z0-9\s\-\’\.]{5,50}$ will match names having anywhere between 5 to 50 characters. These will however not work with non-English accented characters like Düsseldorf or Köln.

Categories
Code

Using Gmail JavaScript API to Search Email Threads

This code uses the Gmail JavaScript API to search the inbox of the authenticated user (userId = me) for email threads that match the specified query. You need to create a new project in Google developer console with the Gmail API enabled.

The scope can be “https://www.googleapis.com/auth/gmail.readonly” since this Gmail app requires only read only access to the user’s mailbox.

var threads = function listGmailThreads(userId, query, callback) {
    var getPageOfThreads = function (request, result) {
        request.execute(function (resp) {
            result = result.concat(resp.threads);
            var nextPageToken = resp.nextPageToken;
            if (nextPageToken) {
                request = gapi.client.gmail.users.threads.list({
                    'userId': userId,
                    'q': query,
                    'pageToken': nextPageToken
                });
                getPageOfThreads(request, result);
            } else {
                callback(result);
            }
        });
    };
    var request = gapi.client.gmail.users.threads.list({
        'userId': userId,
        'q': query
    });
    getPageOfThreads(request, []);
}
Categories
Code

Bounced Email Parsing with Google Scripts

The Google Script scans your Gmail mailbox for messages from mailer-daemon@gmail.com and prepares a bounce email report logging the failed deliveries in a Google Spreadsheet. See sample Gmail bounce report

function getBouncedEmails() {

    /* Written by Amit Agarwal */
    /* Email: amit@labnol.org  */

    // Write the bounced email report to a Google SpreadsheetApp
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).clearContent();

    // Find all emails returned via Gmail Mailer Maemon
    var query = "from:(mailer-daemon@google.com OR mailer-daemon@googlemail.com)";

    // Get the most recent 500 bounced email messages in Gmail
    GmailApp.search(query, 0, 500).forEach(function(thread) {
        thread.getMessages().forEach(function(message) {
            if (message.getFrom().indexOf("mailer-daemon") !== -1) {
                var body = message.getPlainBody();
                // Get the bounced email address from the body
                var matches = body.match(/Delivery to[\s\S]+?(\S+\@\S+)\s([\s\S]+?)----- Original Message/);
                if (matches) {
                    // Get the exact reason for the email bounce
                    var reason = matches[2].match(/The error.+:\s+(.+)/) || matches[2].match(/Technical details.+:\s+(.+)/);
                    if (reason) {
                        // Save the data in a Google Spreadsheet
                        sheet.appendRow([
                            thread.getLastMessageDate(),
                            matches[1],
                            reason[1].replace(/ (Please|Learn|See).*$/, ""),
                            thread.getPermalink(),
                            thread.getFirstMessageSubject()
                        ]);
                    }
                }
            }
        });
    });
}
Categories
Code

Get List of Email Aliases with Gmail API

Gmail users can send emails of behalf of any other email address that is set as an alias in their primary account. The Gmail API also supports these alias addresses for sending emails and the FROM address can either be set to your primary Gmail account or any alias.

The GmailApp service of Google Apps Script offers a simple getAliases() method that returns all the aliases of a Gmail account in an array.

However, this may not return aliases that are not set as “Treat as Alias” in the users’ Gmail setttings. If you would like to fetch all Gmail aliases, included those that are not using the ‘Treat as an alias’ setting, you need to use the Gmail API.

You should also check the verification status of an alias. If the status is set to “pending”, or any value other than “accepted”, you should not use it as it indicates that the user initiated the process for setting that email as an alias but did not complete the verification process.

function getGmailAliases() {

  // Get list of email aliases via the Gmail API  
  // Author Amit Agarwal Website: www.ctrlq.org
  
  var aliases = [];
  
  Gmail.Users.Settings.SendAs.list('me').sendAs.forEach(function(e) {
    if (e.verificationStatus === "accepted") {
      aliases.push({
        email: e.sendAsEmail,
        replyto: e.replyToAddress,
        name: e.displayName,
        alias: e.treatAsAlias  
      })
    }
  });
  
  return aliases;

}

The same method can be used to fetch the Gmail signature of authorized user. You would need to enable the Gmail API under Advanced Google Service for the code to work.

Here’s an alternate approach that uses the Gmail but without the Advanced Gmail Service of Apps Script.

    JSON.parse(UrlFetchApp.fetch("https://www.googleapis.com/gmail/v1/users/me/settings/sendAs", {
      contentType: "application/json",
      headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()}
    }).getContentText()).sendAs.forEach(function(alias) {
      if (alias.verificationStatus === "accepted") {
        aliases.push(alias.sendAsEmail);
      }
    });

Also see: Unable to Add Gmail Aliases

Categories
Code

Extract and Replace Links in HTML with JavaScript RegEx

For the Mail Merge project, I need to extract all the hyperlinks in the email message and append email tracking parameters to each of the links. The links can be either embedded in the HTML <a> tag or they can be mentioned in plain text like example.com – Gmail and other email clients are smart enough to replace such plain text website links into clickable hyperlinks.

I’m using RegEx to pull out these links from HTML / Text and then a simple JavaScript function to manipulate the link.

Replace Links inside HTML Tags

function updateLinksInHTML(html) {
    
  var regex = /href\s*=\s*(['"])(https?:\/\/.+?)\1/ig;   
  var link;
  
  while((link = regex.exec(html)) !== null) {
    html = html.replace(link[2], "https://ctrlq.org?redirect_to" + encodeURIComponent(link[2]));
  }
  
  return html;
  
}

Convert Plain Text into Links

Some text make contain links in plain text and this method would replace such links into clickable hyperlinks by adding the anchor tag.

function createTextLinks_(text) {
  
  return (text || "").replace(
    /([^\S]|^)(((https?\:\/\/)|(www\.))(\S+))/gi,
    function(match, space, url){
      var hyperlink = url;
      if (!hyperlink.match('^https?:\/\/')) {
        hyperlink = 'http://' + hyperlink;
      }
      return space + '' + url + '';
    }
  );
  
};
Categories
Code

How to Publish Google Script as a Web App

You can publish a Google Apps Script as a web app and anyone can run your scripts directly from their browsers. For instance, if you have a file upload Google Form, you can publish the script as a public web app and even anonymous users, who are not logged into their Google accounts, will be able to upload files to your Google Drive.

When deploying a Google Apps Script ha a web app, you can specify who has access to your web app:

  1. Only myself – only the script owner can access the web.
  2. Any member of domain – only members who are part of your Google Apps domain can access the app.
  3. Anyone – Anyone can access the web app, but they need to be logged into their own Google account.
  4. Anyone, including anonymous – Users who do not have Google accounts can also access your web app.

If you are unable to publish your web app for anonymous usage, it is likely that your Google Apps admin has disabled the option for the organization.

Allow users to share outside Google Apps domain

Some Google Apps admins, for privacy reasons, may disallow users in the domain from sharing documents and files outside the organization. In such cases, the option to publish a web app for anonymous usage is unavailable too.

  1. Sign in to the Google Admin console at admin.google.com
  2. Go to Apps > Google Apps > Drive > Sharing settings.
  3. Under Sharing outside of organization, select ON.
  4. Select the option – Allow users in organization to publish files on the web or make them visible to the world as public or unlisted files.

You can use the Google Drive Auditor to audit the sharing permissions of your files and know who has access to Drive.

Categories
Code

How to Import CSV Files into Google Spreadsheets with Google Apps Script

You can easily import CSV files into Google Spreadsheet using the Utilities.parseCsv() method of Google Apps Script. The snippets here show how to parse and import data from CSV files that are on the web, saved on Google Drive or available as a Gmail attachments.

Import CSV from an email attachment in Gmail


function importCSVFromGmail() {
  
  var threads = GmailApp.search("from:reports@example.com");
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];
  
  // Is the attachment a CSV file
  if (attachment.getContentType() === "text/csv") {
    
    var sheet = SpreadsheetApp.getActiveSheet();
    var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");
    
    // Remember to clear the content of the sheet before importing new data
    sheet.clearContents().clearFormats();
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
    
  }
  
}

Import CSV file that exists in Google Drive

function importCSVFromGoogleDrive() {
  
  var file = DriveApp.getFilesByName("data.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  
}

Fetch and import CSV file from an external website

function importCSVFromWeb() {
  
  // Provide the full URL of the CSV file.
  var csvUrl = "https://ctrlq.org/data.csv";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  
}

This method can however not be used for importing CSV files hosted on FTP servers as the UrlFetchApp service of Google Scripts doesn’t allow connecting to FTP servers, yet.

Categories
Code

Spintax (Spin Syntax) with JavaScript

Spintax, short for spin syntax, is a list of text phrases, sentences, and synonyms separated by the pipe (|) character. Each group of keywords is enclosed inside curly ({}) brackets.

The Spintax parser picks a random keyword or sentence from the available choices and generates a unique sentence for each iteration. For instance, if the spintax is {Hello|Hi|Hola}, the output may contain either of these greetings.

Spintax can be nested as well like {{Thanks and|Best}Regards|Cheers}. Here’s a spintax parser written in JavaScript.


var text = "{{Hello|Hi|Hola}, How {have you been|are you doing}? " +
           "Take care. {{Thanks and|Best} Regards|Cheers|Thanks}";

var matches, options, random;

var regEx = new RegExp(/{([^{}]+?)}/);

while((matches = regEx.exec(text)) !== null) {
  options = matches[1].split("|");
  random = Math.floor(Math.random() * options.length);
  text = text.replace(matches[0], options[random]);
}

console.log(text);
Categories
Docs

Why Gmail is not Sending my Email Messages?

Gmail has certain limits in place and you only send or receive a limited number of emails per day. The daily limits are 2000 email messages for Google Apps (G Suite) accounts and 500 for consumer Gmail accounts.

It is important to know that Gmail sending limits are per user and are shared by all of the user’s email clients, including web apps, software programs, SMTP clients and add-ons that could be sending emails through your Gmail account.

For instance, if you have sent 300 emails through the Gmail website, 50 emails through Microsoft Outlook linked to your Gmail account via IMAP or POP, and another 100 messages through an email alias on a different domain, the maximum number of emails you can send through the Mail Merge addon will be just 50 in that 24 hour period.

If you exceed the sending limit, you’ll get an error message – like “you’ve reached a Gmail limit for sending email” or “Oops.. the system encountered a problem” – and Gmail may sometimes temporarily block you from sending new messages. The limits are not applied at any set time of the day and the user may have to wait up to 24 hours before the limits are reset automatically.

[*] The daily sending limit is even lower – 100 total recipients per day – if you are using Microsoft Exchange or any non-Google SMTP service for routing messages through Gmail email servers.

Email Bounced – Your Message Was Not Sent

If you get a bounced email from nobody@gmail.com – with the message saying “An error occurred, your message was not sent” or “You have reached a limit for sending email, your message was not sent” – it indicates that you have reached the Gmail sending limit for the day.

There’s no workaround to this problem and you’ll have to wait until Gmail resets your email quota. After the quota is reset, you can resend the message to the same recipients and they should be delivered as normal.

Your messages can also bounce if you are sending too many emails to non-existent email addresses. It is thus important to clean up your mailing lists and remove invalid email addresses before running another Mail Merge campaign.

Gmail Error – Message Rejected

This happens when Gmail classifies your outgoing email message as spam. This happens when you have spam text in the message body or if you have a large number of recipients in the CC or BCC list.

Categories
Code

Convert Image to Base64 Data URI

In Spreadsheet Paintings, you upload a photograph from the local disk and it transforms the picture into pixel art. Internally, the JavaScript resizes the image using the HTML5 Canvas API and then uploads the base64-encoded representation of the canvas data to the Google Script using the HTMLService where the pixels are converted into RGB colors.




Categories
Code

Convert Excel Files to CSV in Google Drive with Apps Script

The Google Apps Script uses the Advanced Drive API to covert Microsoft Excel files (XLS, XLSX) into CSV files and saves them into a specific Google Drive folder. The Excel sheets are deleted after the CSV files are saved in Drive.

Also see: Convert Google Sheets to PDF Files

The conversion engine may timeout if you have too many XLS/XLSX files in a Google Drive and in that case, you’d need to include the time check to ensure that the script doesn’t exceed the execution time limit.

function convertXLSFilesToCSV() {

    var oauthToken = ScriptApp.getOAuthToken(),
        sourceFolder = DriveApp.getFolderById(SOURCE_XLS_FOLDER),
        targetFolder = DriveApp.getFolderById(TARGET_CSV_FOLDER),
        mimes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];

    /* Written by Amit Agarwal */
    /* email: amit@labnol.org  */
    /* website: www.ctrlq.org */

    for (var m = 0; m < mimes.length; m++) {

        files = sourceFolder.getFilesByType(mimes[m]);

        while (files.hasNext()) {

            var sourceFile = files.next();

            // Re-upload the XLS file after convert in Google Sheet format
            var googleSheet = JSON.parse(UrlFetchApp.fetch(
                "https://www.googleapis.com/upload/drive/v2/files?uploadType=media&convert=true", {
                    method: "POST",
                    contentType: "application/vnd.ms-excel",
                    payload: sourceFile.getBlob().getBytes(),
                    headers: {
                        "Authorization": "Bearer " + oauthToken
                    }
                }
            ).getContentText());

            // The exportLinks object has a link to the converted CSV file
            var targetFile = UrlFetchApp.fetch(
                googleSheet.exportLinks["text/csv"], {
                    method: "GET",
                    headers: {
                        "Authorization": "Bearer " + oauthToken
                    }
                });

            // Save the CSV file in the destination folder
            targetFolder.createFile(targetFile.getBlob()).setName(sourceFile.getName() + ".csv");

            // Delete the processed file
            sourceFile.setTrashed(true);

        }
    }

}
Categories
Docs

How to Email a Range of Google Spreadsheet Cells

The Email Spreadsheet add-on helps you automatically email one or more sheets inside a Google Spreadsheet to multiple people. You can configure the add-on to convert and email individual sheets or the entire spreadsheet.

One of the popular features of the add-on is the ability to email the selected range of cells. For instance, if you have a big sheet but you only wish to email a specific range of cells, you can use the Range option to email the selected cells.

Every cell in a Google Spreadsheet has a Row, Column (R, C) coordinate. For instance, the cell A1 has Row = 1 and column = 1. The cell E8 has Row = 8 and Column = 5.

When configuring the Email Spreadsheet Add-on to convert a range of cells, you need to provide the upper left cell in the range and the lower right cell in RC format. For instance, if you would like export all cells in the A1:E8 range, the R1C1 and R2C2 values would be 1,1,8,5 respectively.

Email Spreadsheet Range

In the above example, the selected range is A5:C12 and therefore the export range is 5(R1), 1(C1), 12(R2), 3(C2)

You can further select advanced options to show / hide gridlines, page numbers, etc. Internally, the add-on is powered by Google Apps Script.

Categories
Code

How to Center a DIV with Flexbox

Centering a div element on a web page is easy with Flexbox. The element is centered both vertically and horizontally and the method uses no JavaScript.

Flexbox is supported in all modern browsers (including mobile) but if your site visitors are still using one of the older versions of Internet Explorer, the CSS approach may be better.




I am the center

Categories
Docs

Google Drive Search for Files

The Google Drive Permissions Auditor add-on uses a Google Script to analyze who has access to your Google Drive files. You can use any of the search operators of the Google Drive API to query for files in Google Drive whose metadata match your search criteria.

1. Find all files in my Google Drive owned by me except those in trash

"me" in owners and trashed = false

2. Search for files that begin with the term “IMG”

name contains 'IMG'

The contains operator only performs prefix matching for a name. For example, the name “HelloWorld” would match for name contains ‘Hello’ but not name contains ‘World’.

3. Search files that contain the word “ctrlq” anywhere in the content

fullText contains 'ctrlq'

The contains operator only performs matching on entire string tokens for fullText. For example, if the full text of a doc contains the string “HelloWorld” only the query fullText contains ‘HelloWorld’ returns a result.

4. Find all image files modified since January 30, 2016

modifiedTime > '2016-01-01T12:00:00' and mimeType contains 'image/'

5. Find all audio & video files that are not owned by me

sharedWithMe and (mimeType contains 'video/' or mimeType contains 'audio/')

6. Search for files containing the text “important” and are in the trash

fullText contains 'important' and trashed = true

7. Search for all Google Sheets in Google Drive (Supported MIME Types)

mimeType = 'application/vnd.google-apps.spreadsheet'

8. Search for files where abc@example.com is an editor (writer)

'abc@example.com' in writers

9. Search files that do not contain the word “ctrlq” in the content

fullText not contains 'ctrlq'

10. Search files that are not JPG images

mimeType != 'image/jpeg'

If you need help with writing search queries, please contact the developer.

Categories
Code

How to Learn Google Apps Script

The best resource for learning Google Script is the official documentation available at developers.google.com. Here are other video tutorials and online learning resources where you can get up to speed using Google Apps Script that will let you programmatically access various Google Apps and services include Gmail, Google Drive, Calendar, Google Forms, Google Docs and more.

Also see: How to Learn Programming Online

  1. Most Useful Google Scripts by +Amit Agarwal
  2. Google Scripts for Google Apps – a collection on Google+
  3. Google Script Projects Case Studies on google.com
  4. Google Apps Script – Code Samples
  5. scoop.it/t/gas by Martin Hawksey
  6. GAS for Developers – Video training by Bruce McPherson.
  7. O’Reilly – Apps Script – by James Ferreira
  8. Apps Script Webinars – YouTube – by +Eric Koleda
  9. StackOverflow Q&A – Got a question? Ask here.
  10. lynda.com – Scott Simpson (Up and Running with Google Scripts)
  11. Tutorial – Written Google Docs Addons
  12. Known Issues & Bugs – Report a bug here.
Categories
Code

Download Unsplash Photos to Google Drive

Unsplash is the best source for free images on the Internet. The images have the Creative Commons zero license meaning you can do anything with the photos.

This Google Script uses the Unsplash API to fetch the most recently uploaded photos and downloads them to your Google Drive. The photo details, like the height, width, creator name, full RAW link, etc. are appended to a Google Spreadsheet.

You can set this is a time-based trigger to automatically save all the new Unsplash photos in your Google Drive. Change the page parameter to download all the old pictures as well. You would however need to create your CLIENT_ID for the API call.

function getUnsplashPhotos() {

    try {

        var ss = SpreadsheetApp.getActiveSheet();

        // Fetch a maximum of 30 photos per API call
        var url = "https://api.unsplash.com/photos/?client_id=API_CLIENT_ID&per_page=30&page=1";

        // Parse the JSON response in an Array
        var photos = JSON.parse(UrlFetchApp.fetch(url).getContentText());

        for (var p = 0; p < photos.length; p++) {

            var categories = [],
                photo = photos[p];

            for (var c = 0; c < photo.categories.length; c++) {
                categories.push(photo.categories[c].title);
            }

            var blob = UrlFetchApp.fetch(photos.urls.full).getBlob();

            var file = DriveApp.createFile(blob);

            file.setName(photos.user.name);

            var row = [
                photo.id,
                photo.created_at.substr(0, 10),
                categories.join(", "),
                photo.width,
                photo.height,
                photo.color, // Main Color Hex Mode
                photo.likes, // How popular is the photograph
                photo.user.name, // Credit the photographer
                photo.user.links.html,
                photo.urls.raw, // Full high res version URL
                photo.urls.full,
                file.getUrl() // URL of the photo in Google Drive
            ];

            ss.appendRow(row);

        }

    } catch (f) {
        Logger.log(f.toString());
    }
}

Categories
Code

Copy Email Messages in Gmail with Apps Script

This example shows how to create email copies in Gmail with Google Apps Script and the Gmail API. The requires requires authorization with the scope https://mail.google.com/ for reading Gmail drafts and composing new ones.

The uploadType is set to media and that will create the Gmail draft in a single HTTP request. However, if you are create a draft that includes big attachment, you may have to set the upload type as resumable to create the draft in at least two requests.

function duplicateGmailDrafts(count, draftId) {

    try {

        var draft = GmailApp.getMessageById(draftId);

        if (draft === null) {
            return "Sorry, the draft message was not found. Please reload this page and try again.";
        }

        var gmailAPI = "https://www.googleapis.com/upload/gmail/v1/users/me/drafts?uploadType=media";

        var params = {
            method: "POST",
            contentType: "message/rfc822",
            muteHttpExceptions: true,
            headers: {
                "Authorization": "Bearer " + ScriptApp.getOAuthToken()
            },
            payload: draft.getRawContent()
        };

        for (var i = 0; i < count; i++) {
            UrlFetchApp.fetch(gmailAPI, params);
        }

        return "Drafts Created";

    } catch (f) {

        return "Error: " + f.toString();

    }

}
Categories
Code

Upload and Post Images to Twitter with Google Script

There are two ways to post tweets that contain images. You can either upload the picture(s) to an image hosting service and then paste the image URL into the tweet. The other option is that you natively upload the image into Twitter.

Here’s a sample snippet that shows how you can use Google Apps Script to upload and post images to Twitter with the new Twitter API. You can either pull an image from the web or you can use the DriveApp service to pull an image from your Google Drive.

To get started, you need to create a new Twitter app and generate the Consumer API keys. You’ll also need to include the Twitter Library in your Apps Script project (key MKvHYYdYA4G5JJHj7hxIcoh8V4oX7X1M_)

function sendTweetwithImage() {
  
  var twitterKeys= {
    TWITTER_CONSUMER_KEY: "aa",
    TWITTER_CONSUMER_SECRET: "bb",
    TWITTER_ACCESS_TOKEN: "cc",
    TWITTER_ACCESS_SECRET: "cc"    
  };
  
  var props = PropertiesService.getUserProperties();
  
  props.setProperties(twitterKeys);
  
  var twit = new Twitter.OAuth(props);
  
  if ( twit.hasAccess() ) {
    
    try {
      
      // DriveApp.getFileById(id).getBlob()
      
      var imageUrl  = "http://img.labnol.org/di/M1.jpg";    
      var imageBlob = twit.grabImage(imageUrl, "image/jpeg");
      var uploadImg = twit.uploadMedia(imageBlob);
      
      if (uploadImg) {
        
        var status = "Hello @labnol";
        
        var response = twit.sendTweet(status, {
          media_ids: uploadImg.media_id_string      
        });
        
        if (response) {
          
          Logger.log("Tweet Sent " + response.id_str);
          
        } else {
          
          // Tweet could not be sent
          // Go to View -> Logs to see the error message
          
        }
        
      }
    } catch (f) {
      Logger.log(f.toString());
    }
    
  }  
}
Categories
Code

Copy Google Spreadsheet Data to another Sheet with Apps Script

The cloneGoogleSheet() function will copy data (all rows and columns, but no formatting styles) from one Google Spreadsheet to any other Google Spreadsheet under the same Google Drive.

You need specify the file IDs of the source and destination Google Spreadsheets as arguments in the formula and also change the source and target sheet names inside the method body.

This function can be invoked via a time-based trigger or run it manually from the Apps Script editor. However, if you would like to keep the two spreadsheet in sync with each other always, you an consider using the IMPORTRANGE() Google formula that automatically imports a range of cells from a specified spreadsheet into the currently selected cell / range /sheet.


// copy data from Google Sheet A to Google Sheet B
// Credit: @chrislkeller

function cloneGoogleSheet(ssA, ssB) {

  // source doc
  var sss = SpreadsheetApp.openById(ssA);

  // source sheet
  var ss = sss.getSheetByName('Source spreadsheet');
  
  // Get full range of data
  var SRange = ss.getDataRange();

  // get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();

  // get the data values in range
  var SData = SRange.getValues();

  // target spreadsheet
  var tss = SpreadsheetApp.openById(ssB);
  
  // target sheet
  var ts = tss.getSheetByName('Target Spreadsheet'); 

  // Clear the Google Sheet before copy
  ts.clear({contentsOnly: true});

  // set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);

};
Categories
Docs

Google Add-ons Not Working for Google Apps

If your Google Add-ons are not working as expected or if you are unable to install Google Docs add-ons from the Chrome store, it is likely that your Google Apps admin has disabled the setting that allows domain users to use add-ons.

Google Apps Add-ons

Here’s how they can enable add-ons for your Google Account from the Google Apps admin dashboard.

Step 1 – Login to your Google Apps admin console at admin.google.com

Step 2 – Go to Apps -> Google Apps -> Drive and make sure the status is “ON” for everyone.

Step 3 – Go to Apps -> Google Apps -> Settings for Google Drive -> Data Access and enable the following settings.

– Drive SDK (Allow users to install Google Drive apps)
Google Drive apps allow users to open their files in web apps installed from the Chrome Web Store.

– Add-Ons (Allow users to install Google Docs add-ons from add-ons store)
Docs add-ons allow users to use Docs features built by other developers.

The Admin console setting for add-ons controls Docs, Sheets, and Forms; there are not separate settings for each document type.

Categories
Code

Send SMS with Google Scripts and Twilio

The Twilio service helps you send SMS and MMS programmatically. They have a REST API that you can call through Google Apps Script and send SMS text messages from your Google Apps based projects. For instance, you can get a text notification on a mobile phone when a Google Form is submitted. Or you can send short text messages to multiple people from a Google Spreadsheet similar to Mail Merge.

To get started, you need to register for an account at Twilio (they have trial accounts too) and send text any phone number in the world via Google Scripts. You will use your Twilio account SID as the username and your auth token as the password for HTTP Basic authentication.


/*

Send SMS via #AppsScript
=========================

Written by Amit Agarwal
Website: ctrlq.org
Email: amit@labol.org
Twitter: @labnol

*/

function sendSMS(toNumber, fromNumber, smsText) {

  if (smsText.length > 160) {
    Logger.log("The text should be limited to 160 characters");
    return;
  }

  var accountSID = "ctrlq.sid";
  var authToken = "ctrlq.token";

  var url = "https://api.twilio.com/2010-04-01/Accounts/" + accountSID + "/Messages.json";

  var options = {
    method: "POST",
    headers: {
      Authorization: "Basic " + Utilities.base64Encode(accountSID + ":" + authToken)
    },
    payload: {
      "From" : fromNumber,
      "To"   : toNumber,
      "Body" : smsText
    },
    muteHttpExceptions: true
  };

  var response = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());

  if (response.hasOwnProperty("sid")) {
    Logger.log("Message sent successfully.");
  }

  Utilities.sleep(1000);

}

Sending SMS with Twilio – Notes

1. The recipient’s phone number should be formatted with a ‘+’ and always include the country code e.g., +16175551212 (E.164 format).
2. The SMS body should be less than 160 characters else Twillo would split the text into multiple messages.
3. The sender’s phone number should be a valid Twilio phone number. You cannot put just any mobile phone number to prevent spoofing.

It is important to add sleep between consecutive SMS sending calls as Twilio will only send out messages at a rate of one message per phone number per second.

Categories
Code

Post Google Forms Reponse into Insightly CRM

An organization uses Google Forms to capture leads and would like to automatically post the form submissions as open leads into Insightly for pursual by their sales team. This can be easily done with the help of Google Apps Script that gets triggered automatically on form submit.

Open the Google Form, go to Script Editor from the menu and paste the source code. You’ll need to use your own Insightly API key that can be found under “User Settings” (click the profile icon in the upper right corner of your Insightly dashboard).

/*

Google Forms to Insightly
=========================

Written by Amit Agarwal
Website: ctrlq.org
Email: amit@labol.org
Twitter: @labnol

*/

function setupTriggers() {

  var form = FormApp.getActiveForm();
  ScriptApp.newTrigger("ctrlqFormSubmit").forForm(form).onFormSubmit().create();

}

function ctrlqFormSubmit(e) {

  try {

    var i = 0,
      lead = {},
      items = e.response.getItemResponses();

    for (i = 0; i < items.length; i++) {

      var title = items[i].getItem().getTitle();
      var answer = items[i].getResponse().toString();

      switch (title) {
        case "Company Name":
          lead.ORGANIZATION_NAME = answer;
          break;
        case "First Name":
          lead.FIRST_NAME = answer;
          break;
        case "Last Name":
          lead.LAST_NAME = answer;
          break;
        case "Phone Number":
          lead.PHONE_NUMBER = answer;
          break;
        case "Email Address":
          lead.EMAIL_ADDRESS = answer;
          break;
      }
    }

    var key = "ctrlq-org";

    var response = UrlFetchApp.fetch(
      "https://api.insight.ly/v2.2/Leads", {
        method: "POST",
        payload: JSON.stringify(lead),
        headers: {
          'Authorization': 'Basic ' + Utilities.base64Encode(key),
          'Content-Type': 'application/json'
        }
      }
    );

    Logger.log(response.getContentText());

  } catch (error) {

    Logger.log(error.toString());

  }

}

The above Google Form uses standard contact us fields like name, email address and phone number that can be directly mapped to the standard lead in Insightly. If you have others questions in Google Forms, you can use the custom fields to map them into an Insightly lead. For support, email amit@labnol.org

Categories
Code

Make AJAX Request to Google Script Web App with jQuery

You have published a Google Apps Script as a public web app that returns data as JSON but when you try to make an AJAX call to this web app using jQuery, you get the “Cross Origin” error.

Your AJAX request is blocked by the browser because of the “same origin policy” that disallows reading the remote resource at script.google.com. An easy workaround to this problem is JSONP or JSON with a prefix. With JSONP, the client’s browser won’t enforce the same origin policy but for that to work, you would need to modify your Google Script web app to return results in JSONP format.

Here’s an example web app that return JSONP results.

function doGet(e) {
  
  var result = "";
  
  try {
    result = "Hello " + e.parameter.name;
  } catch (f) {
    result = "Error: " + f.toString();
  }
  
  result = JSON.stringify({
    "result": result
  });  
  
  return ContentService
  .createTextOutput(e.parameter.callback + "(" + result + ")")
  .setMimeType(ContentService.MimeType.JAVASCRIPT);   

}

The MimeType of the output is set as JAVASCRIPT and that will return as JSONP. You can now call this web app from your client side JavaScript as shown here.



Categories
Code

A Custom Google Spreadsheet Function for Tracking Pageviews

GOOGLEANALYTICS is a custom Google Spreadsheet function that tracks Spreadsheet views with the help of Google Apps Script. It includes the JSDoc @customfunction tag and thus the function will autocomplete when begin typing in a spreadsheet cell.

/**
 * Track Spreadsheet views with Google Analytics
 *
 * @param {string} gaaccount Google Analytics Account like UA-1234-56.
 * @param {string} spreadsheet Name of the Google Spreadsheet.
 * @param {string} sheetname Name of individual Google Sheet.
 * @return The 1x1 tracking GIF image
 * @customfunction
 */

function GOOGLEANALYTICS(gaaccount, spreadsheet, sheetname) {
  
  /** 
  * Written by Amit Agarwal 
  * Web: www.ctrlq.org 
  * Email: amit@labnol.org 
  */
  
  /* Random ID to prevent browser caching */
  var cache_buster = Math.round(Date.now() / 1000).toString();
  
  /* Client ID to anonymously define the device */
  var client_id = Utilities.getUuid();
  
  // Event Category set to Google Spreadsheets
  var event_category = encodeURIComponent("Google Spreadsheets");
  
  // Event Action set to spreadsheet title
  var event_action = encodeURIComponent(spreadsheet || "Spreadsheet");
  
  // Event Label set to sheet title
  var event_label = encodeURIComponent(sheetname || "Sheet");
  
  var imageURL = [
    "https://ssl.google-analytics.com/collect?v=1&t=event",
    "&tid=" + gaaccount,
    "&cid=" + client_id,
    "&z="   + cache_buster,
    "&ec="  + event_category,
    "&ea="  + event_action,
    "&el="  + event_label
  ].join("");
  
  return imageURL;

}
Categories
Code

Call JavaScript Function by String Name

You can use eval() method to invoke a JavaScript function whose name is stored in a string variable but there’s a better method that doesn’t require eval.

Let’s say we have a function helloWorld(e) that takes variable and prints it.

function helloWorld(e) {
  e = e || "Anonymous";
  console.log("Hello " + e);
  return;
}

We declare a variable that has the function name and another variable that stores the arguments.

// Function name to invoke
var fnName = "helloWorld";

// Params to pass to the function
var params = "ctrlq.org"

// Call function using Window object
window[fnName](params);

This can be useful for invoking Google Apps Script functions as well. However, since it is server-side code and not a web browser environment, we use this to refer to the object that contains it.

this[fnName](params);
Categories
Code

Create Google Calendar Event with File Attachment

The Google Script will create a new event in the specified Google Calendar and will attach a file from Google Drive into the event.

Google Calendar API only allows file attachments from Google Drive and you can include a maximum of 25 attachment per event. The attachments can be specified either by File ID or by File URL. The Advanced Calendar API should be enabled from your Google Console.


// Credits / References
// https://developers.google.com/google-apps/calendar/v3/reference/events
// http://stackoverflow.com/questions/34853043

function createEvent() {

  var calendarId = '{{Google Calendar Id}}';

  // April 20, 2016 10:00:00 AM
  var start = new Date(2016, 3, 20, 10, 0, 0);

  // April 20, 2016 10:30:00 AM
  var end = new Date(2016, 3, 20, 10, 30, 0);

  var fileName = "Appraisal Guidlines.pdf";

  // Get the Drive ID of the file attachments
  // Only Google Drive file are support in Google Calendar
  var fileId = DriveApp.getFilesByName(fileName).next().getId();

  var calendarEvent = {
    summary: 'Performance Appraisal',
    description: 'Submit Appraisal Document for March.',
    location: '10 Hanover Square, NY 10005',
    start: {
      dateTime: start.toISOString()
    },
    end: {
      dateTime: end.toISOString()
    },
    attachments: [{
      'fileId': fileId,
      'title': fileName
    }],
    attendees: [{
      email: 'employee1@ctrlq.org'
    }, {
      email: 'employee2@labnol.org'
    }]
  };

  // Set supportsAttachments to true 
  // if the calendarEvent object has one or more file attachments
  calendarEvent = Calendar.Events.insert(calendarEvent, calendarId, {
    supportsAttachments: true
  });

  Logger.log('Event with attachment created. Event ID is %s' + calendarEvent.getId());

  // For debugging the output
  Logger.log(calendarEvent); 
  
}
Categories
Code

Get QuickBooks Data into Google Sheets with Apps Script

A recent project involved pulling payments, invoices and accounting data from QuickBooks online into a Google Spreadsheet in near real-time. The integration was done through Google Apps Script and the QuickBooks API (v3). You also need to include OAuth 1.0 library in your Google Script project (QBO doesn’t support the OAuth 2.0 protocol yet).

To get started, go to your QuickBooks Sandbox, create a sample app and get the Consumer Key and Consumer Secret. Next authorize the connection to let Google Sheets access your company inside QuickBooks. The companyId will be stored as a property inside Google Scripts and all subsequent API calls will be made for the authorized company.

Here’s a sample snippet that fetches the invoices data from QuickBooks into a Google Spreadsheet. We’ve added a filter in the SELECT query to only fetch invoices that were created in the last hour. You can set this is a time-based trigger to auto-fetch QuickBooks data into the spreadsheet.


// Written by Amit Agarwal
// email: amit@labnol.org
// web: www.ctrlq.org

function getInvoicesFromQuickBooks() {

  try {

    var service = getQuickBooksService_();

    if (!service || !service.hasAccess()) {
      Logger.log("Please authorize");
      return;
    }

    var props = PropertiesService.getUserProperties(),
      companyId = props.getProperty('QuickBooks.companyID');

    var date = new Date(new Date().getTime() - 1000 * 60 * 60).toISOString();
    var query = "SELECT * FROM Invoice WHERE Metadata.CreateTime > '" + date + "'";

    var url = 'https://quickbooks.api.intuit.com/v3/company/';
    url = +companyId + '/query?query=' + encodeURIComponent(query);

    var response = service.fetch(url, {
      muteHttpExceptions: true,
      contentType: "application/json",
      headers: {
        'Accept': 'application/json'
      }
    });

    var result = JSON.parse(response.getContentText());

    var invoices = result.QueryResponse.Invoice;

    for (var i = 0; i < invoices.length; i++) {

      var Invoice = invoices[i];

      sheet.appendRow(
        [
          Invoice.Id,
          Invoice.time,
          Invoice.Deposit,
          Invoice.DocNumber,
          Invoice.DepartmentRef.name,
          Invoice.CustomerRef.name,
          Invoice.ShipAddr.Line1,
          JSON.stringify(Invoice.Line),
          Invoice.ShipDate,
          Invoice.TrackingNum,
          Invoice.PaymentMethodRef.name,
          Invoice.TotalAmt,
          Invoice.Balance
        ]
      );

    }

  } catch (f) {

    log_("INVOICES ERROR: " + f.toString());

  }
}

The script can be further enhanced to extract details of individual line items like the SKU / Part number, Quantity left, and so. This would however require a separate Rest API call to the following endpoint.

https://quickbooks.api.intuit.com/v3/company/companyId/item/' + itemId
Categories
Code

Authorization Errors with Apps Script Execution API

A web form was built using the Google Apps Script Execution API and it would only be using to users who have successfully authenticated using their Gmail / Google Apps account. The form data would go into a Google Spreadsheet but, in some case, when the user would submit the form, the script would throw an error.

Authorization is required to perform that action.

This is puzzling because the user has already authenticated through Google OAuth 2.0 and the error is not consistent either. The error 401 Invalid Credentials suggest that the OAuth access token you’re using with the project is either expired or invalid.

The auth token provided by Google automatically expires in one hour. Thus if a person has authenticated the form but leave it unattended for more than an hour, the token would automatically expire and the Google API would return an error saying that authorization is required.

An easy workaround would be to auto refresh the token every 45 minutes. This can be done by calling gapi.auth.authorize with the client ID, the scope and immediate:true as parameters.


// OAuth Token expires every hour, 
// so refresh every 45 minutes

window.setInterval(refreshOAuthToken, 1000*60*45);

function refreshOAuthToken() {
  gapi.auth.authorize({
    'client_id': CLIENT_ID,
    'scope': SCOPES,
    'immediate': true
  }, function(r) {
    console.log("OAuth Token Refreshed");
  });
}

You can go to the Chrome developer’s console and use the expires_at field to know how much time is left before the token will expire.

new Date(gapi.auth.getToken().expires_at * 1000)

Call refreshOAuthToken() and the expires_at field with advance by 60 minutes.

Categories
Code

Use the Twitter Search API without the OAuth Library

Google doesn’t recommend using script libraries inside add-ons based Apps script projects as they tend to impact performance. This snippet shows how to use the Twitter Search API inside Google Apps Script without including the OAuth library.

Create a new Twitter app, get the Consumer Key and Secret and you will be able to perform most Twitter API functions from within Google Apps Script.

function testTwitterConnection() {

  var twitterKeys = {
    TWITTER_CONSUMER_KEY: "iqoWfLEG1Q4eMGptxiEzb83Da",
    TWITTER_CONSUMER_SECRET: "g6EJijC9Nsrc2D6WazXjF353FNATZzCvtCoigtBoUMfCQeW0L"
  };

  setupTwitter(twitterKeys);
  searchTwitter('ctrlq');
  
}

function setupTwitter(twitterKeys) {

  // URL encode the consumer key and the consumer secret according to RFC 1738
  var encodedConsumerKey = encodeURIComponent(twitterKeys.TWITTER_CONSUMER_KEY);
  var encodedConsumerSecret = encodeURIComponent(twitterKeys.TWITTER_CONSUMER_SECRET);

  // Concatenate the encoded consumer key, a colon character “:”, and the encoded consumer secret into a single string.
  // Base64 encode the string from the previous step.
  var base64EncodedBearerToken = Utilities.base64Encode(encodedConsumerKey + ":" + encodedConsumerSecret);

  // Step 2: Obtain a bearer token

  // The request must be a HTTP POST request.
  // The request must include an Authorization header with the value of Basic .
  // The request must include a Content-Type header with the value of application/x-www-form-urlencoded;charset=UTF-8.
  // The body of the request must be grant_type=client_credentials.

  var bearer_url = 'https://api.twitter.com/oauth2/token';
  var options = {
    "method": "POST",
    "headers": {
      "Authorization": "Basic " + base64EncodedBearerToken,
      "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8"
    },
    "payload": {
      "grant_type": "client_credentials"
    }
  };

  var response = UrlFetchApp.fetch(bearer_url, options);
  var data = JSON.parse(response.getContentText());

  // Store the Access Token
  if (data.access_token) {
    PropertiesService.getScriptProperties()
      .setProperty("TWITTER_ACCESS_TOKEN", data.access_token);
  }

  return data.access_token;
}


function searchTwitter(query) {

  var access_token = PropertiesService.getScriptProperties().getProperty("TWITTER_ACCESS_TOKEN");

  if (access_token === null) {
    Logger.log("Run Twitter setup again");
    return;
  }

  var base_url = 'https://api.twitter.com/1.1/search/tweets.json';
  var search_url = base_url + '?q=' + encodeURIComponent(query);

  var options = {
    "method": "GET",
    "headers": {
      "Authorization": "Bearer " + access_token,
      "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
      "Accept-Encoding": "gzip"
    },
    "followRedirects": true,
    "muteHttpExceptions": true
  };

  var response = UrlFetchApp.fetch(search_url, options);
  var data = JSON.parse(response.getContentText());
  var tweets = data.statuses;

  // Output the tweets in the log
  // They can also be saved in a Google Spreadsheet

  for (var t = 0; t < tweets.length; t++) {
    Logger.log("%s wrote: %s", tweets[t].user.name, tweets[t].text);
  }

}
Categories
Code

Use the Gmail API to Send Emails with Attachments

This example shows how you can easily send email messages with file attachment using the Gmail API. The attachments can be stored in Google Drive and you need to specify the file IDs that are to included in the outgoing messages.

We begin by creating a MIME message that complies with RFC 2822 standard and call the Gmail API to sends the specified message to the recipients in the To, Cc, and Bcc headers. We use the /upload URI with the messages/send method for uploading the files with the message and the uploadType is set to media for uploading the files without any metadata.

The code is written in Google Apps Script but Google also offers Gmail API Client libraries for PHP, Python, JavaScript and NodeJS. If you wish to include images, upload them to a site like imgur and include them in the email’s HTML body using img tags.

// Written by Amit Agarwal
// Web: ctrlq.org
// Email: amit@labnol.org

function sendEmailWithAttachments() {

  var attachments = ["File_ID_1", "File_ID_2"];

  var message = {
    to: {
      name: "Google Scripts",
      email: "amit@labnol.org"
    },
    from: {
      name: "Amit Agarwal",
      email: "amit@labnol.org"
    },
    body: {
      text: "Mr hänn is schon lang nümme g'she.",
      html: "Mr hänn is schon lang nümme g'she."
    },
    subject: "ctrlq, tech à la carte",
    files: getAttachments_(attachments)
  };

  // Compose Gmail message and send immediately
  callGmailAPI_(message);

}


function callGmailAPI_(message) {

  var payload = createMimeMessage_(message);

  var response = UrlFetchApp.fetch(
    "https://www.googleapis.com/upload/gmail/v1/users/me/messages/send?uploadType=media", {
      method: "POST",
      headers: {
        "Authorization": "Bearer " + ScriptApp.getOAuthToken(),
        "Content-Type": "message/rfc822",
      },
      muteHttpExceptions: true,
      payload: payload
    });

  Logger.log(response.getResponseCode());
  Logger.log(response.getContentText());

}

// UTF-8 characters in names and subject
function encode_(subject) {
  var enc_subject = Utilities.base64Encode(subject, Utilities.Charset.UTF_8);
  return '=?utf-8?B?' + enc_subject + '?=';
}

// Insert file attachments from Google Drive
function getAttachments_(ids) {
  var att = [];
  for (var i in ids) {
    var file = DriveApp.getFileById(ids[i]);
    att.push({
      mimeType: file.getMimeType(),
      fileName: file.getName(),
      bytes: Utilities.base64Encode(file.getBlob().getBytes())
    });
  }
  return att;
}

// Create a MIME message that complies with RFC 2822
function createMimeMessage_(msg) {

  var nl = "\n";
  var boundary = "__ctrlq_dot_org__";

  var mimeBody = [

    "MIME-Version: 1.0",
    "To: "      + encode_(msg.to.name) + "<" + msg.to.email + ">",
    "From: "    + encode_(msg.from.name) + "<" + msg.from.email + ">",
    "Subject: " + encode_(msg.subject), // takes care of accented characters

    "Content-Type: multipart/alternative; boundary=" + boundary + nl,
    "--" + boundary,

    "Content-Type: text/plain; charset=UTF-8",
    "Content-Transfer-Encoding: base64" + nl,
    Utilities.base64Encode(msg.body.text, Utilities.Charset.UTF_8) + nl,
    "--" + boundary,

    "Content-Type: text/html; charset=UTF-8",
    "Content-Transfer-Encoding: base64" + nl,
    Utilities.base64Encode(msg.body.html, Utilities.Charset.UTF_8) + nl

  ];

  for (var i = 0; i < msg.files.length; i++) {

    var attachment = [
      "--" + boundary,
      "Content-Type: " + msg.files[i].mimeType + '; name="' + msg.files[i].fileName + '"',
      'Content-Disposition: attachment; filename="' + msg.files[i].fileName + '"',
      "Content-Transfer-Encoding: base64" + nl,
      msg.files[i].bytes
    ];

    mimeBody.push(attachment.join(nl));

  }

  mimeBody.push("--" + boundary + "--");

  return mimeBody.join(nl);

}
Categories
Code

Convert and Email Google Spreadsheets as PDF Files

You can set up a cron job in Google Drive using Google Apps Script that will send any Google Spreadsheet, or any other document or file in the Drive, to one or more email addresses at a specified time. You can set the time-based trigger to run weekly, daily, every hour and other recurring schedules.

This example shows how to send a Google Spreadsheet to the specified email address automatically as a PDF file daily. The Google Script converts the Google Spreadsheet into a PDF file and sends it to another email address using your own Gmail account. You can further customize the PDF output – like remove gridlines, hide frozen rows, change to landscape mode, etc. by setting the correct export parameters.

Convert & Email Google Sheets

The Email Google Spreadsheet add-on can automatically convert and email spreadsheets in PDF, CSV or Microsoft Excel (xlsx) formats. It can convert the entire spreadsheet or individual sheets.

The premium version of the add-on can automatically email the converted sheets on a recurring schedule (like every hour, daily, weekly or monthly). You can also set up multiple sending schedules and automatically email the sheet to a different set of receipts at different times.

Google Script to Email Google Spreadsheets

If you cannot use the Email Spreadsheet add-on (some Google Apps admins do not allow add-ons), you can write your own Google Script to email the spreadsheet as PDF files.


/* Send Spreadsheet in an email as PDF, automatically */
function emailSpreadsheetAsPDF() {
  // Send the PDF of the spreadsheet to this email address
  const email = Session.getActiveUser().getEmail() || 'amit@labnol.org';

  // Get the currently active spreadsheet URL (link)
  // Or use SpreadsheetApp.openByUrl("<>");
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Subject of email message
  const subject = `PDF generated from spreadsheet ${ss.getName()}`;

  // Email Body can  be HTML too with your logo image - see ctrlq.org/html-mail
  const body = "Sent with Email Google Sheets";

  // Base URL
  const url = 'https://docs.google.com/spreadsheets/d/SS_ID/export?'.replace('SS_ID', ss.getId());

  /* Specify PDF export parameters
    From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
    */

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
    '&size=letter' + // paper size legal / letter / A4
    '&portrait=false' + // orientation, false for landscape
    '&fitw=true&source=labnol' + // fit to page width, false for actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid='; // the sheet's Id

  const token = ScriptApp.getOAuthToken();
  const sheets = ss.getSheets();

  // make an empty array to hold your fetched blobs
  const blobs = [];

  for (let i = 0; i < sheets.length; i += 1) {
    // Convert individual worksheets to PDF
    const response = UrlFetchApp.fetch(url + exportOptions + sheets[i].getSheetId(), {
      headers: {
        Authorization: `Bearer ${token}`
      }
    });

    // convert the response to a blob and store in our array
    blobs[i] = response.getBlob().setName(`${sheets[i].getName()}.pdf`);
  }

  // create new blob that is a zip file containing our blob array
  const zipBlob = Utilities.zip(blobs).setName(`${ss.getName()}.zip`);

  // optional: save the file to the root folder of Google Drive
  DriveApp.createFile(zipBlob);

  // Define the scope
  Logger.log(`Storage Space used: ${DriveApp.getStorageUsed()}`);

  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 0)
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [zipBlob]
    });
}

The Google Script function will convert each of the worksheets of a Google spreadsheet into a PDF file, compresses all the PDF files in a ZIP file and sends it to a designated email address. You can send to multiple email addresses too – just separate each of them with a comma.

The method currently sends all the sheets of a Spreadsheet in the ZIP file but you can also specify a sheet ID with the &gid parameter to email a particular sheet only. For instance, to send the first sheet, you can set the gid=0 and so on.

Convert Full Google Sheet to a PDF File

The above snippet converts individual sheets into separate PDF files but there’s also a way to convert the entire spreadsheet into a single PDF file. In that case, replace guid= with id=SS_ID (spreadsheet ID) or perform the conversion using DriveApp as shown here.

/* Email Google Spreadsheet as PDF */
function emailGoogleSpreadsheetAsPDF() {
  
  // Send the PDF of the spreadsheet to this email address
  var email = "amit@labnol.org"; 
  
  // Get the currently active spreadsheet URL (link)
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Subject of email message
  var subject = "PDF generated from spreadsheet " + ss.getName(); 

  // Email Body can  be HTML too 
  var body = "Sent via Email Spreadsheet";
  
  var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");
  
  blob.setName(ss.getName() + ".pdf");
  
  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 0) 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments:[blob]     
    });  
}
Categories
Code

Move File to a Different Folder in Google Drive

Google Apps Script doesn’t offer a method for moving files from one folder in Google Drive to another one but there’s an option to add a file in Drive to multiple folders (similar to symbolic links in Unix). That can be used for moving files as well – remove the file from all existing folders and add it to the target folder.


// Move file to another Google Drive Folder

function moveFileToAnotherFolder(fileID, targetFolderID) {

  var file = DriveApp.getFileById(fileID);
  
  // Remove the file from all parent folders
  var parents = file.getParents();
  while (parents.hasNext()) {
    var parent = parents.next();
    parent.removeFile(file);
  }

  DriveApp.getFolderById(targetFolderID).addFile(file);
  
}
Categories
Code

Convert Google Sheet to Excel XLSX Spreadsheet

The previous example shows how to convert Google Sheets to XLS format using the Google Drive API. The response file resource includes exportLinks URLs for the various export formats for Google Spreadsheets. For instance, the Microsoft Excel version of the Google Sheet can be retrieved via this link:

file['exportLinks']['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']

You need to enabled Advanced Drive API in your Google Developers Console project to know the Export URL of a Google Drive file but there’s a way to get the Excel version using the DriveApp service as well.

The getGoogleSpreadsheetAsExcel() method will convert the current Google Spreadsheet to Excel XLSX format and then emails the file as an attachment to the specified user.

function getGoogleSpreadsheetAsExcel(){
  
  try {
    
    var ss = SpreadsheetApp.getActive();
    
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";
    
    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };
    
    var blob = UrlFetchApp.fetch(url, params).getBlob();
    
    blob.setName(ss.getName() + ".xlsx");
    
    MailApp.sendEmail("amit@labnol.org", "Google Sheet to Excel", "The XLSX file is attached", {attachments: [blob]});
    
  } catch (f) {
    Logger.log(f.toString());
  }
}

Categories
Code

Update Gmail Picture of Google Apps Users with Google Script

An organisation is running on Google Apps and, as part of a branding exercise, they need to update the profile pictures of all Gmail users. The Google Apps admin can use the Admin Directory API with Google Apps Script to update Gmail photos of all users in the domain. The files can be placed in Google Drive and the script can automatically make it the user’s photo.

The Apps Admin Directory API accepts images in JPEG, PNG, GIF, BMP and TIFF formats. The user is identified by their primary email address and the photo’s upload data should be encoded in web-safe Base64 format.

function updateGmailPhotoFromDrive() {
  
  try {
    
    var userEmail = 'amit@labnol.org';
    var fileName  = userEmail + ".jpg";
    
    var blob = DriveApp.getFilesByName(fileName).next().getBlob();
    var data = Utilities.base64EncodeWebSafe(blob.getBytes());
    
    AdminDirectory.Users.Photos.update({photoData: data}, userEmail);
    
  }
  
  catch (err) {
    Logger.log(err.toString());
  }
  
}

If an image is on the web (and not on Google Drive), the URL Fetch service can be used to make it the user’s photo.

function updateGmailPhotoFromWeb() {
  
  try {
    
    var userEmail = 'amit@labnol.org';
    var webURL    = 'https://ctrlq.org/assets/ctrlq.org.png';
    
    var blob = UrlFetchApp.fetch(webURL).getBlob();
    var data = Utilities.base64EncodeWebSafe(blob.getBytes());
    
    AdminDirectory.Users.Photos.update({photoData: data}, userEmail);
    
  }
  
  catch (err) {
    Logger.log(err.toString());
  }
  
}
Categories
Code

Update Gmail Signatures of Employees with Google Apps Script

Google Apps allows domain administrators to update the Gmail signatures programatically. This helps you maintain a standard email signature for all users of your organisation but certain fields like employee’s name, email address, title or phone number can be variable.

The Email Settings API is used for creating or retrieving Gmail Signatures and it is only available for Google Apps for Work accounts. Also, you need to have administrator privileges to update company-wide Gmail settings such as signatures. You can use both plain text and rich HTML signatures.

1. Create a HTML file with the signature template. The entry tag contains the apps:property tag for signature.



    

2. Paste this in the code.gs file. If you would like to update the signature of all Google Apps users, use the Google Admin SDK (AdminDirectory.Users.list()) to get a list of all users and loop through the list.

function updateGmailSignature() {
    
  var email = "amit@labnol.org"; // User's email address
  var html = "Howdy! My email signature!"; // HTML signature
  
  setEmailSignature(email, html);
  
}

// Create an HTML encoded string
function createPayload_(html) {
  
  var str = html.replace(/&/g, "&")
  .replace(//g, ">")
  .replace(/'/g, "'")
  .replace(/"/g, """);
  
  return HtmlService.createHtmlOutputFromFile("template")
  .getContent()
  .replace("SIGNATURE", str)
  
}

function getAPI_(email) {
  
  var scope = 'https://apps-apis.google.com/a/feeds/emailsettings/2.0/',
      user = email.split("@");
  
  return Utilities.formatString('%s%s/%s/signature', scope, user[1], user[0]);  
  
}

function updateEmailSignature(email, html) {
  
  var response = UrlFetchApp.fetch(getAPI_(email), {
    method: "PUT",
    muteHttpExceptions: true,
    contentType: "application/atom+xml",
    payload: createPayload_(html),
    headers: {
      Authorization: 'Bearer ' + getSignaturesService_().getAccessToken()
    }    
  });
  
  if (response.getResponseCode() !== 200) {
    Logger.log("ERROR: " + response.getContentText());
  } else {
    Logger.log("Signature updated");
  }
   
}

You would also need to include the Oauth2 library with the scope set as https://apps-apis.google.com/a/feeds/emailsettings/2.0/ for the email signature service.

You can also append standard legal disclaimers to the email signatures with this technique. First retrieve the existing Gmail signature of a Google App user, append the text and update the signature.


/* Retrieve existing Gmail signature for any Google Apps user */

function getEmailSignature(email) {
  
  var response = UrlFetchApp.fetch(getAPI_(email), {
    method: "GET",
    muteHttpExceptions: true,
    headers: {
      Authorization: 'Bearer ' + getSignaturesService_().getAccessToken()
    }    
  });
  
  if (response.getResponseCode() !== 200) {
    Logger.log("ERROR: " + response.getContentText());
  }
  
  return response.getContentText();
  
}
Categories
Code

Send Emails with Attachments with Google Apps Script and Mandrill

Gmail sending limits are restricted especially when you are sending emails programatically as in the case of Mail Merge. You can only send a few hundred emails per day and then you’ve to wait the entire day for Google to reset your limit.

If you would like to send thousands of emails but without the daily restrictions, you can consider using an email sending service like Mandrill. You need a web domain and need to verify the ownership of that domain with Mandrill so that you are allowed to send emails via Mandrill. Once verified, you get an API key from Mandrill that you can use with Apps Script to send emails in bulk without restrictions.

Here’s a sample snippet that sends emails from Google Scripts via Mandrill. You also have to option to include file attachments in your emails and these files can be placed in a Google Drive folder. The sendEmail() method accepts both plain text and HTML Mail.

function sendEmail() {
  
  var MANDRILL_API_KEY = "<>";
  
  var files = [
    "<>",  
    "<>",  
    "<>"  
  ];

  var recipients = [
    {
      "email": "ctrlq+to@labnol.org",
      "name": "Amit Agarwal",
      "type": "to"
    }, {
      "email": "ctrlq+cc@labnol.org",
      "type": "cc"
    }, {
      "email": "ctrlq+bcc@gmail.com",
      "type": "bcc"
    }
  ];
  
  var attachments = [];
  
  for (var f in files) {
    var file = DriveApp.getFileById(files[f]);
    attachments.push({
      "type": file.getMimeType(),
      "name": file.getName(),
      "content": Utilities.base64Encode(file.getBlob().getBytes())
    });
  }
  
  var params = {
    "key": MANDRILL_API_KEY,
    "message": {
      "from_email": "<>",
      "from_name": "<>",
      "to": recipients,
      "attachments": attachments,
      "headers": {
        "Reply-To": "reply@example.com"
      },
      "subject": "Enter email subject",
      "text"   : "Enter email body in plain text",
      "html"   : "Enter HTML content with tags"
    }
  };

  var response = UrlFetchApp.fetch(
    "https://mandrillapp.com/api/1.0/messages/send.json", {
      'method': 'POST',
      'payload': JSON.stringify(params),
      'contentType': 'application/json'
    });

  Logger.log(response.getContentText());
}

It may take some time to build your mail domain reputation and hence the emails are queued and not sent instantly. Go to Mandrill Dashboard – Outbound Emails – Activity to see the current status of your sent Emails.

Mandrill Email Open Report

Also, it is no longer possible to send emails from generic addresses like @gmail.com or @outlook.com since Mandrill requires domain ownership verification to reduce spam emails.

Categories
Code

How to Write JSON to a Google Spreadsheet

The WriteJSONtoSheet method take a JSON object and appends it as a new row in a Google Spreadsheet. It takes the keys of the JSON object, matches them with the header row of the spreadsheet and arranges the columns accordingly. If there’s no column corresponding to a key, it is automatically added. This is handy for logging operations and data objects in a Google Sheet for easy analysis.


// Written by Amit Agarwal www.ctrlq.org

function writeJSONtoSheet(json) {

  var sheet = SpreadsheetApp.getActiveSheet();

  var keys = Object.keys(json).sort();
  var last = sheet.getLastColumn();
  var header = sheet.getRange(1, 1, 1, last).getValues()[0];
  var newCols = [];

  for (var k = 0; k < keys.length; k++) {
    if (header.indexOf(keys[k]) === -1) {
      newCols.push(keys[k]);
    }
  }

  if (newCols.length > 0) {
    sheet.insertColumnsAfter(last, newCols.length);
    sheet.getRange(1, last + 1, 1, newCols.length).setValues([newCols]);
    header = header.concat(newCols);
  }

  var row = [];

  for (var h = 0; h < header.length; h++) {
    row.push(header[h] in json ? json[header[h]] : "");
  }

  sheet.appendRow(row);

}
Categories
Code

How to Suspend Execution of a Google App Script While it is Running

A commonly requested feature of Mail Merge was that users wanted the ability to stop the merge process (and thus the underlying Google Script) after hitting the start button.

The maximum execution time limit of any Google Apps Script is about 5 minutes and the script will terminate itself automatically after the time is up. If you are running a script manually from the Google Script Editor, you can click “Cancel” to abort a running script but this option is not available when the script is running through an HTML Service powered web app or as a Google Add-on.

Here’s a little snippet that will show you how to abruptly stop a running script that is executing from outside the Script Editor. The idea is that you set up a property when the Stop button is pressed. The running script watches this property value and if it is set to “STOP”, the script pauses.

The HTML:






The Server (HTML is served as a web app)

function startScript() {
  do {
    Logger.log("Script running");
    Utilities.sleep(5000);
  } while (keepRunning());
  return "OK";
}

function keepRunning() {
  var status = PropertiesService.getScriptProperties().getProperty("run") || "OK";
  return status === "OK" ? true : false;
}

function stopScript() {
  PropertiesService.getScriptProperties().setProperty("run", "STOP");
  return "Kill Signal Issued";
}

function doGet(e) {
  PropertiesService.getScriptProperties().setProperty("run", "OK");
  return HtmlService.createHtmlOutputFromFile('html')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

Also see: How to Stop Google Scripts

Categories
Code

Check if a Date is Valid with JavaScript

An HTML form requires users to select the year, month and date in separate dropdown fields without using a date picker. The developer needs to ensure that the values selected by the users form a valid date. For instance, an input like 2 (Month), 30 (Date) should be rejected.

HTML Valid Date

function isValidDate() {

  var day = Number(document.getElementById("day").value),
    month = Number(document.getElementById("month").value),
    year = Number(document.getElementById("year").value);

  var date = new Date();
  date.setFullYear(year, month - 1, day);
  // month - 1 since the month index is 0-based (0 = January)

  if ( (date.getFullYear() == year) && (date.getMonth() == month + 1) && (date.getDate() == day) )
    return true;

  return false;
}
Categories
Code

Create Google Contacts from Google Voice Mail

Google Voice sends you an email notification where there’s a missed call or a voicemail for you. The email includes the caller’s name, the caller’s phone number and, in case of voicemails, the text transcript of the message. The message also includes a link to download the Google Voice MP3.

The Google Scripts scans all your Google Voice emails, parses the content uses regex and creates a new Google contact. The transcript of the voice message is set to the notes field of the new Google contact.

Link: Save Google Voice Messages to Google Drive

// Written by Amit Agarwal www.ctrlq.org
// Email: amit@labnol.org

// Search Google Voicemail emails
function searchGoogleVoiceEmails() {

  var threads = GmailApp.search("from:voice-noreply@google.com", 0, 100);

  for (var t = 0; t < threads.length; t++) {

    var response = extractVoicemail_(threads[t].getMessages()[0]);

    if (response) {
      createContact_(response);
    }

  }
}

// Extract the caller's name, phone number and voice message transcription
function extractVoicemail_(msg) {

  var result = {
    "Message Date": msg.getDate(),
    "Message Subject": msg.getSubject(),
    "Message Body": msg.getPlainBody().replace(/<[^>]+>/g, "").replace(/\s+/g, " "),
    "Transcription": msg.getPlainBody()
  };

  var trans = /transcript:(.*)?play message/i.exec(result["Message Body"]);

  if (trans) result.Transcription = trans[1];

  //Voicemail from: John Q Public (202) 123-456 at 6:08 PM

  var match = /(Missed Call|Voicemail) from:([\*\+\s\w]+)([\d\-\s\(\)\+]*)? at [\d\:\s]+[ap]m/i.exec(result["Message Body"]);

  if (match) {
    result["Call Type"] = match[1];
    result["Contact Name"] = match[2].replace(/^\+/, "");
    result["Contact Number"] = match[3].replace(/^\+/, "");
  } else {
    return null;
  }

  return result;
}

// Create a new Google contact from Voicemail
function createContact_(result) {

  var contacts = ContactsApp.getContactsByPhone(result["Contact Number"], ContactsApp.Field.WORK_PHONE);

  if (contacts.length > 0) {

    return "Contact Exists";

  } else {

    var contact = ContactsApp.createContact(result["Contact Name"], result["Contact Name"], result["Call Type"]);

    contact.addPhone(ContactsApp.Field.WORK_PHONE, result["Contact Number"]);
    contact.setNotes(result["Transcription"]);

    return contact.getId();

  }
}
Categories
Code

Use the Google Translate API for Free

The official Google Translate API is available for businesses only but you can use Google Apps Script to create your own Google Language Translation API without having to pay the enterprise license fee.

The text can be translated from one language to another using the LanguageApp service or, if you run out of quota, you can make a call to the secret translate.googleapis.com API that is internally used by the Google Translate extension for Chrome and requires no authentication.

You can publish the Google script and deploy it as a web app with parameters for source and target languages and the text query. You can specify any ISO language pair or say “auto” and the Google Translation API will auto detect the language of the source text.


/* Written by Amit Agarwal */
/* web: ctrlq.org          */

function doGet(e) {

  var sourceText = ''
  if (e.parameter.q){
    sourceText = e.parameter.q;
  }
  
  var sourceLang = 'auto';
  if (e.parameter.source){
    sourceLang = e.parameter.source;
  }

  var targetLang = 'ja';
  if (e.parameter.target){
    targetLang = e.parameter.target;
  }
  
  /* Option 1 */
  
  var translatedText = LanguageApp.translate(sourceText, sourceLang, targetLang)
  
  /* Option 2 */  
  
  var url = "https://translate.googleapis.com/translate_a/single?client=gtx&sl=" 
            + sourceLang + "&tl=" + targetLang + "&dt=t&q=" + encodeURI(sourceText);
  
  var result = JSON.parse(UrlFetchApp.fetch(url).getContentText());
  
  translatedText = result[0][0][0];
  
  var json = {
    'sourceText' : sourceText,
    'translatedText' : translatedText
  };
  
  // set JSONP callback
  var callback = 'callback';
  if(e.parameter.callback){
    callback = e.parameter.callback
  }
  
  // return JSONP
  return ContentService
           .createTextOutput(callback + '(' + JSON.stringify(json) + ')')
           .setMimeType(ContentService.MimeType.JAVASCRIPT);
}
Categories
Code

Validate an Email Address with PHP

Before sending email via SMTP, the client computer initiates a transaction with the SMTP email server that is listening for commands on TCP port 25. The SMTP command HELO initiates a transaction with the mail server and identifies itself, MAIL FROM and RCTP specify the sender and recipients respectively while QUIT will close the conversation. If the mail server returns the status as 250, that means the email address is validate and exists.

@hbattat has written a wrapper PHP library that can be used to determine if an email address is real or not. You specify the sender’s email, the recipient’s email and connect to the mail server to know whether that email exists on the domain or not. Email verification can be done using Windows Telnet as well.

\r\n");
            $from = fgets($connect, 1024);
            $details .= $from."\n";

                        // Send the SCPT command with the recepient's email address
            fputs($connect, "RCPT TO: <$toemail>\r\n");
            $to = fgets($connect, 1024);
            $details .= $to."\n";

            // Close the socket connection with QUIT command to the SMTP server
            fputs($connect, 'QUIT');
            fclose($connect);

            // The expected response is 250 if the email is valid
            if (!preg_match('/^250/i', $from) || !preg_match('/^250/i', $to)) {
                $result = 'invalid';
            } else {
                $result = 'valid';
            }
        }
    } else {
        $result = 'invalid';
        $details .= 'Could not connect to server';
    }
    if ($getdetails) {
        return array($result, $details);
    } else {
        return $result;
    }
}

SMTP also provides the VRFY command to query when the specified user exists on the host or not but it is often ignored by mail servers.

Also see: Find Person by Email Address

Categories
Code

Display RSS Feed on a Web Page with Google Feed

You can use the Google Feeds API to fetch and display the content of any RSS feed on a web page. The feed URL can be specified in the div container using HTML5 data attributes.




  
    
    Show RSS Feed
    
    
  
  

  
    
Loading RSS Feed..
Categories
Code

Get the Amazon Sales Rank by ASIN in Google Spreadsheet

Given the Amazon ASIN number, the getAmazonSalesRank() method with return the overall Sales Rank of the item using the Amazon Product Advertising API. You’ll need to enter your own Amazon AWS keys and associate ID before making the API calls.

This can also be converted into a custom function for Google Spreadsheets where you can enter the product ASINs in one column the latest sales rank is displayed in another column. It is however recommended that you either use the Cache Service or store the results in Property Service to avoid making too many calls to the Amazon API.

function getAmazonSalesRank(asin) {

  try {

    var method = "GET",
        uri    = "/onca/xml",
        host   = "ecs.amazonaws.com",
        public_key    = "YOUR_PUBLIC_KEY",
        private_key   = "YOUR_PRIVATE_KEY",
        associate_tag = "YOUR_AMAZON_ASSOCIATE_ID";

    var params = {
      Service        : "AWSECommerceService",
      Version        : "2011-08-01",
      AssociateTag   : associate_tag,
      Operation      : "ItemLookup",
      ItemId         : asin,
      Timestamp      : new Date().toISOString(),
      AWSAccessKeyId : public_key,
      ResponseGroup  : "SalesRank"
    };

    var canonicalized_query = Object.keys(params).sort();

    canonicalized_query = canonicalized_query.map(function(key){
      return key + "=" + encodeURIComponent(params[key]);
    });

    var string_to_sign = method + "\n" + host + "\n" + uri + "\n" + canonicalized_query.join("&");

    var signature = Utilities.base64Encode
    (Utilities.computeHmacSha256Signature(string_to_sign, private_key));

    var request = "http://" + host + uri + "?" 
                     + canonicalized_query.join("&") + "&Signature=" + encodeURIComponent(signature);

    var response = UrlFetchApp.fetch(request, {muteHttpExceptions: true}).getContentText();

    var elems = XmlService.parse(response).getDescendants();

    for(var i=0; i

		
Categories
Code

Create RSS Feed for YouTube Search Results

Based upon the Twitter RSS Feed, a Maryland based Rails developers has created a Google Script that creates a RSS feed of YouTube videos matching your search terms. You pass the search phrase as a parameter to the Google Script web app and it uses the ContentService to serve the results as an RSS feed.

Before authorizing the code, go to the Google Developers console and enable the YouTube Data API for your Apps Script project. You may also be required to create credentials before using the YouTube API in Apps Script. Publish the script as web app and set access to anyone, even anonymous.


/*
  YouTube RSS Feeds
  Written by @user1535152 http://stackoverflow.com/q/30486682/512127
  Based on http://www.labnol.org/internet/twitter-rss-feed/28149/  
*/

function doGet(e) {
    
  var title  = ("Youtube RSS Feed for " + e.parameter.search),
      timez  = Session.getScriptTimeZone(),
      search = encodeURIComponent(e.parameter.search),
      link   = ("https://www.youtube.com/results?search_query=" + search),
      self   = ScriptApp.getService().getUrl() + "?" + search;
        
  var rss='';
  rss+='';
  rss+=''+title+'';
  rss+=''+link+'';
  rss+='';
  rss+='' + title + ' updated on ' + new Date() + '.';
  
  var results = YouTube.Search.list('id, snippet', {
    q: search,
    maxResults: 50,
    order: 'date'
  });
  
  for (var i = 0; i < results.items.length; i++){
    var item = results.items[i];
    rss += "";
    rss += "" + item.snippet.title + "";
    rss += "http://www.youtube.com/watch?v=" + item.id.videoId + "";
    rss += "" + item.snippet.description + "";
    rss += "" + Utilities.formatDate(new Date(item.snippet.publishedAt), timez, "EEE, dd MMM yyyy HH:mm:ss Z") + "";
    rss += "http://www.youtube.com/watch?v=" + item.id.videoId + "";
    rss += "";
  }
  
  rss+="";
  
  return ContentService.createTextOutput(rss).setMimeType(ContentService.MimeType.RSS);
}

Categories
Code

Convert Word, Excel and PowerPoint files to Google Docs with Google Script

You can store your Microsoft Office files (Word Documents, PowerPoint Presentations and Excel Spreadsheets) in Google Drive in their native format but then it takes up storage space, the files cannot be edited in the cloud and you’ll not be able to embed the files on other web page.

For instance, you can embed a Google Sheet, or a portion of it, in your web page but not if the file is in the xls or xlsx format. A simple solution therefore would be to convert the Office documents into the corresponding Google Document formats and this can be easily done with Google Apps Script.

This Google Script will convert Office files to the Google format using the Advanced Drive API. It then renames the converted document to the original filename but without the extension. You will have to enable the Advance Drive API for your Apps Script project through the Google Developers Console.


// Written by Amit Agarwal www.ctrlq.org
// Email: amit@labnol.org

function convertDocuments() {
  
  // Convert xlsx file to Google Spreadsheet
  convertToGoogleDocs_("Excel File.xlsx")

  // Convert .doc/.docx files to Google Document
  convertToGoogleDocs_("Microsoft Word Document.doc")

  // Convert pptx to Google Slides
  convertToGoogleDocs_("PowerPoint Presentation.pptx")

}

// By Google Docs, we mean the native Google Docs format
function convertToGoogleDocs_(fileName) {
  
  var officeFile = DriveApp.getFilesByName(fileName).next();
  
  // Use the Advanced Drive API to upload the Excel file to Drive
  // convert = true will convert the file to the corresponding Google Docs format
  
  var uploadFile = JSON.parse(UrlFetchApp.fetch(
    "https://www.googleapis.com/upload/drive/v2/files?uploadType=media&convert=true", 
    {
      method: "POST",
      contentType: officeFile.getMimeType(),
      payload: officeFile.getBlob().getBytes(),
      headers: {
        "Authorization" : "Bearer " + ScriptApp.getOAuthToken()
      },
      muteHttpExceptions: true
    }
  ).getContentText());
  
  // Remove the file extension from the original file name
  var googleFileName = officeFile.substr(0, officeFile.lastIndexOf("."));
  
  // Update the name of the Google Sheet created from the Excel sheet
  DriveApp.getFileById(uploadFile.id).setName(googleFileName);
  
  Logger.log(uploadFile.alternateLink);  
}

The files are created the root folder of Google Drive.

Categories
Code

Share Files in Google Drive without Email Notifications

The File.addViewer() or File.addEditor() methods of Google Apps Script allow you to share files in Google Drive programmatically. These methods will however always send an email notification to the person with whom the file is being shared.

If you would like to share a file in Google Drive silently without sending them an email notification, the advanced Google Drive API can help. The Permissions.insert() method lets you specify sharing permissions for a file. The role can be either owner, writer or editor. The additionalRoles[] should be sent to commenter to add someone as a commenter.

When calling Drive.Permissions.insert(), specify the advanced option “sendNotificationEmails” to “false”. You’ll have to enable this however in your Google Apps Script project.





 Drive.Permissions.insert(
   {
     'role': 'writer',
     'type': 'user',
     'value': 'bob@example.com'
   },
   fileId,
   {
     'sendNotificationEmails': 'false'
   });

The method can be used to disable email notification when sharing folders too.



var folderid = folder.getId();

folder.setSharing(accessinfo, permissioninfo);

if (editors.length > 0) {
  for (var i = 0; i < editorsemails.length; i++) {
    Drive.Permissions.insert(
      {
        'role': 'writer',
        'type': 'user',
        'value': editorsemails[i]
      },
      folderid,
      {
        'sendNotificationEmails': 'false'
      });
  };
};

if (viewers.length > 0) {
  for (var i = 0; i < viewersemails.length; i++) {
    Drive.Permissions.insert(
      {
        'role': 'reader',
        'type': 'user',
        'value': viewersemails[i]
      },
      folderid,
      {
        'sendNotificationEmails': 'false'
      });
  };
};
Categories
Code

Save Paypal Email Receipts in Google Spreadsheet

When you make a purchase through PayPal, the payment company sends you an email receipt with the details of the transaction. The Google script will scan your Gmail mailbox for all Paypal receipts, extracts the details from the message body using regex and saves them in a Google Spreadsheet for quick reference. The script extracts the transaction ID, the item purchased, the shipping cost and other details.

Also see: Sell Digital Goods with Paypal and Google Drive

function searchGmail() {
  
  var threads = GmailApp.search("from:paypal", 0, 10);
  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  for (var t=0; t(.*?)<\/td>(.*?)<\/a><\/td>(.*?)<\/td>(.*?)<\/td>(.*?)<\/td><\/tr>/g.exec(html);  
    if (match) {
      result["Item #"] = match[1];
      result["Item Title"] = match[2];
      result["Quantity"] = match[3];
      result["Price"] = match[4];
      result["Subtotal"] = match[5];
    }
    
    match = /Shipping & Handling:\s+\(.*?\)(.*?)\s+Shipping/g.exec(body);  
    if (match) 
      result["Shipping and Handling"] = match[1];
    
    
    match = /Shipping Insurance.*?:(.*?)\s+Total:\s*(.*? .*?)\s+/g.exec(body);  
    if (match) {
      result["Shipping Insurance"] = match[1];
      result["Total"] = match[2];
    }
    
    match = /credit card statement as "(.*?)".*?purchased from:(.*?)\s+Receipt id:([\d\-]+)/gi.exec(body);  
    if (match) {
      result["Name in Statement"] = match[1];
      result["Purchased From"] = match[2];
      result["Receipt ID"] = match[3];
    }
    
    match = /international shipping.*?Total:(.*?)\s+.*credit card statement as "(.*?)"/gi.exec(body);  
    if (match) {
      result["International Shipping Total"] = match[1];
      result["International Name in Statement"] = match[2];
    }
    
  return result;
  
}
Categories
Code

Indian Numbering Function for Google Sheets

If you would like to represent numbers using the Indian Numbering system (lakhs, crores) inside a Google Spreadsheet, the INR() custom function by Amit Wilson will help. Pass the number as a parameter to the INR() function and it will instantly write the number in words using the lakhs and crores system.

Indian Rupee in Google Spreadsheets

You’ll have to paste the code inside the Script Editor of your Google Spreadsheet. The function is written in JavaScript so it can be used in HTML / JS apps as well.




function INR(input) {

  var a, b, c, d, e, output, outputA, outputB, outputC, outputD, outputE;

  var ones = ['', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine'];

  if (input === 0) { // Zero  

    output = "Rupees zero";

  } else if (input == 1) { // One

    output = "Rupee one only";

  } else { // More than one

    // Tens
    a = input % 100;
    outputA = oneToHundred_(a);

    // Hundreds
    b = Math.floor((input % 1000) / 100);
    if (b > 0 && b < 10) {
      outputB = ones[b];
    }

    // Thousands
    c = (Math.floor(input / 1000)) % 100;
    outputC = oneToHundred_(c);

    // Lakh
    d = (Math.floor(input / 100000)) % 100;
    outputD = oneToHundred_(d);

    // Crore
    e = (Math.floor(input / 10000000)) % 100;
    outputE = oneToHundred_(e);

    // Make string
    output = "Rupees";

    if (e > 0) {
      output = output + " " + outputE + " crore";
    }

    if (d > 0) {
      output = output + " " + outputD + " lakh";
    }

    if (c > 0) {
      output = output + " " + outputC + " thousand";
    }

    if (b > 0) {
      output = output + " " + outputB + " hundred";
    }

    if (input > 100 && a > 0) {
      output = output + " and";
    }

    if (a > 0) {
      output = output + " " + outputA;
    }

    output = output + " only";
  }

  return output;

}




function oneToHundred_(num) {

  var outNum;

  var ones = ['', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine'];

  var teens = ['ten', 'eleven', 'twelve', 'thirteen', 'fourteen', 'fifteen', 'sixteen', 'seventeen', 'eighteen', 'nineteen'];

  var tens = ['', '', 'twenty', 'thirty', 'forty', 'fifty', 'sixty', 'seventy', 'eighty', 'ninety'];

  if (num > 0 && num < 10) { // 1 to 9

    outNum = ones[num]; // ones

  } else if (num > 9 && num < 20) { // 10 to 19

    outNum = teens[(num % 10)]; // teens

  } else if (num > 19 && num < 100) { // 20 to 100

    outNum = tens[Math.floor(num / 10)]; // tens

    if (num % 10 > 0) {

      outNum = outNum + " " + ones[num % 10]; // tens + ones

    }

  }

  return outNum;

}
Categories
Code

Apps Script Execution API Error – Permission Denied

The Apps Script Execution API lets you call Google Apps Scripts from standard HTML pages. I’ve been working on a project that involved Apps Script Execution API, it was running perfect in the developer account but as soon as the code was migrated to the client’s Google account, it failed.

{
  "error": {
    "status": "PERMISSION_DENIED", 
    "message": "The caller does not have permission", 
    "code": 403
  }
}

The Google APIs were enabled in the Google Developers Console, the scopes were correct, the Apps Script Execution API was published with “Anyone” account but the script would fail each time the user tried authentication.

  var op = gapi.client.request({
    'root': 'https://script.googleapis.com',
    'path': 'v1/scripts/' + SCRIPT_ID + ':run',
    'method': 'POST',
    'body': request
  });

Turned out the problem was somewhere else. The Request Body that is sent with the HTTP POST request has a parameter called devMode. The default is false but is generally set to true in the development mode to save the developer from having to publish a new version every time a change is made to the code.

  var request = {
    'function': 'ctrlq',
    'parameters': [],
    'devMode': true // Should be set to FALSE
  };

Remember to set devMode to false during deployment and the Permission Denied error would be fixed.

Categories
Code

Extract Text from Images with Google Drive OCR

Google Drive supports OCR for image and PDF uploads. That means if you upload a, say, JPEG file containing text, Google Drive can automatically extract the text from the image and save it to an editable Google Document. OCR search is also available in Microsoft OneNote and Evernote except that with Google Docs, the converted text can be saved as well.

There are online tools that let you convert images to text using OCR but did you know that you can use Google Apps Script to easily build a similar tool for free. Build a form that accepts file uploads, publish as a web app and then send the file to Google Drive via Apps Script. The server side script can OCR the image and return the extracted text as output.


/* Credit: https://gist.github.com/tagplus5 */

function doGet(request) {

  var status;

  if (request.parameters.url !== undefined && request.parameters.url !== "") {

    try {

      // Fetch the image data from the web
      var imageBlob = UrlFetchApp.fetch(request.parameters.url).getBlob();

      var resource = {
        title: imageBlob.getName(),
        mimeType: imageBlob.getContentType()
      };

      // OCR on .jpg, .png, .gif, or .pdf uploads
      var options = {
        ocr: true
      };

      var docFile = Drive.Files.insert(resource, imageBlob, options);

      var doc = DocumentApp.openById(docFile.id);

      // Extract the text body of the Google Document
      var text = doc.getBody().getText().replace("\n", "");

      // Send the document to trash
      Drive.Files.remove(docFile.id);

      status = text;

    } catch (error) {

      status = "ERROR: " + error.toString();

    }

  } else {

    status = "ERROR: No image url specified in the HTTP request";
  }

  return ContentService.createTextOutput(status);

}
Categories
Code

Upload Files using the Multipart Post Method with Google Script

This code snippet shows how you can use the use the multipart post method to upload a file from Google Drive to Box using the Box API and Google Script. The PDF file is already on Google Drive, it gets the blob of the file using the File_ID and uploads to a specific Box folder (FOLDER_ID).


// Written by Amit Agarwal www.labnol.org

function uploadFile() {
  
  var boundary = "labnol";
  var blob = DriveApp.getFileById(GOOGLE_DRIVE_FILE_ID).getBlob();
  
  var attributes = "{\"name\":\"abc.pdf\", \"parent\":{\"id\":\"FOLDER_ID\"}}";
  
  var requestBody = Utilities.newBlob(
    "--"+boundary+"\r\n"
    + "Content-Disposition: form-data; name=\"attributes\"\r\n\r\n"
    + attributes+"\r\n"+"--"+boundary+"\r\n"
    + "Content-Disposition: form-data; name=\"file\"; filename=\""+blob.getName()+"\"\r\n"
  + "Content-Type: " + blob.getContentType()+"\r\n\r\n").getBytes()
  .concat(blob.getBytes())
  .concat(Utilities.newBlob("\r\n--"+boundary+"--\r\n").getBytes());
  
  var options = {
    method: "post",
    contentType: "multipart/form-data; boundary="+boundary,
    payload: requestBody,
    muteHttpExceptions: true,
    headers: {'Authorization': 'Bearer ' + getBoxService_().getAccessToken()}
  };

  var request = UrlFetchApp.fetch("https://upload.box.com/api/2.0/files/content", options);

  Logger.log(request.getContentText());

}

Unlike Google Drive that allows multiple files of the same name, Box is more restrictive. It rejects files that have names longer than 255 characters or duplicate files with the same name.

The HTTP multipart request is commonly used to upload files and other data over to a HTTP Server. A “multipart/form-data” message contains a series of parts separated by boundaries. Each part should contain the “Content-Disposition” header whose value is “form-data” and if a file is being sent to the server, the contentType should also be included.

If the same request is made with curl, the request will be:

curl https://upload.box.com/api/2.0/files/content \
  -H "Authorization: Bearer ACCESS_TOKEN" -X POST \
  -F attributes='{"name":"file.pdf", "parent":{"id":"FOLDER_ID"}}' \
  -F file=@file.pdf
Categories
Code

Connect to Box API with Google Apps Script

You can use the OAuth2 library with Google Apps script to connect to the Box API. The Box API is available to all users and provide full access (read and write) to your Box account.

To get started, create a new Box application at developers.box.com and set the redirect_uri to your Google Apps Script project. Next include the OAuth2 library in your Google Script and call authorizeBox() from the Run menu to authorize the connection between your Google Account and Box account.

The getFolderList method will logs a list of all folders that are in the root folder of your Box account.


// Written by Amit Agarwal www.ctrlq.org

// Step 1.
function authorizeBox() {
  var service = getBoxService_();
  if (!service.hasAccess()) {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL to authorize: %s',
               authorizationUrl);
  } else {
    Logger.log('Your account is already authorized');
  }
}

// Step 2.
function getFoldersList() {
  
  var response = UrlFetchApp.fetch('https://api.box.com/2.0/folders/0/items?fields=name,type', {
    headers: {
      'Authorization': 'Bearer ' + getBoxService_().getAccessToken()
    }
  });
  
  var result = JSON.parse(response.getContentText());
  var items = result.entries;
  
  var folders = [];
  
  for (var i=0; i

		
Categories
Code

Build a Charts Dashboard with Google Sheets and HTML Service

The school has a Google Form where teacher enter the performance grades of their students. This form data is stored in a Google Spreadsheet and they are using Google Charts with the Google Visualization API to convert these tables of rows into visual charts that can be easily visualized.

The principal needs a public dashboard (in other words, a web page) where the charts can be displayed to external users without having to give them access to the Google Spreadsheet. This can be easily done with the HTML Service of Google Script and the Google Visualization API.

Here’s a simple example that fetches data from a Google Spreadsheet and display the corresponding chart on a web page using the HTML Service. The Google script needs to be published as a Web App and the access should be set to anyone (including anonymous) or you can also limit it to users of your own Google Apps Domain.

google-charts-dashboard


// Code.gs
function doGet(e) {
  
  return HtmlService
  .createTemplateFromFile("index")
  .evaluate()
  .setTitle("Google Spreadsheet Chart")
  .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  
}

function getSpreadsheetData() {
  
  var ssID   = "PUT_YOUR_SPREADSHEET_ID",
      sheet  = SpreadsheetApp.openById(ssID).getSheets()[0],
      data   = sheet.getDataRange().getValues();
  
  return data;
    
}

Next create an html file inside the script editor and save it as index.html






  




  

The above example fetches the rows of a single sheet but if your dashboard is complex and requires you to fetch data from multiple sheets in a spreadsheet, you can use the JSON form.

On the server side, create a JSON of your data and pass that to the HTML template after converting the JSON into a string (using JSON.stringify). On the client side, parse the string to convert it into JSON (using JSON.parse) and build your charts and graphs.

Categories
Code

Import CSV File into Google Cloud SQL from Cloud Storage

You can upload one or more CSV files to a specific bucket in Google Cloud Storage and then use Google Apps Script to import the CSV files from Cloud Storage into your Google Cloud SQL database.

In the method here, the CSV file is deleted from Cloud Storage after the import operation is complete. You can however call the /copyTo/ endpoint to move the CSV files into another Cloud Storage folder after processing.

It is important to add a wait (sleep) function because the API will throw an error if you begin uploading another file while the previous import operation is pending. The file names must be encoded as well.


function uploadtoCloudSQL() {

  // Written by Amit Agarwal amit@labnol.org
  // Web: www.ctrlq.org

  var service = getService();

  if (!service.hasAccess()) {
    Logger.log(service.getAuthorizationUrl());
    return;
  }

  var token = service.getAccessToken();

  // Getting list of files to be processed
  var result = JSON.parse(
    UrlFetchApp.fetch('https://www.googleapis.com/storage/v1/b/BUCKET_NAME/o', {
      method: "GET",
      headers: {
        Authorization: 'Bearer ' + token
      }
    }).getContentText());

  for (var i = 0; i < result.items.length; i++) {
    if (result.items[i].name.indexOf(".") !== -1) {
      files.push(result.items[i].name);
    }
  }

  for (var f = 0; f < files.length; f++) {

    var path = files[f].split("/");

    var payload =
      '{"importContext" : { "csvImportOptions": {"table":"MY_TABLE"}, "fileType": "CSV", "database": "MY_DATABASE", "uri": "gs://BUCKET_NAME/FOLDER/CSVFILE"}}'
      .replace("FOLDER", path[0])
      .replace("CSVFILE", path[1]);

    UrlFetchApp.fetch('https://www.googleapis.com/sql/v1beta4/projects/PROJECT/instances/INSTANCE/import', {
      method: "POST",
      contentType: "application/json",
      headers: {
        Authorization: 'Bearer ' + token
      },
      payload: payload,
      muteHttpExceptions: true
    });

    UrlFetchApp.fetch('https://www.googleapis.com/storage/v1/b/BUCKET_NAME/o/' + encodeURIComponent(files[f]), {
      method: "DELETE",
      headers: {
        Authorization: 'Bearer ' + token
      }
    });

    // Wait for the previous import job to end
    Utilities.sleep(5000);

  }

}

Categories
Code

Autoreply to Filtered Gmail Messages with Google Scripts

This Google Script, originally published by @rboyd, scans for lengthy emails in your Gmail mailbox (messages that have more than 100 words) and sends an auto-reply requesting the sender to resend the email after editing to less than 100 words. The long email is archived (moved away from the Inbox) after the auto-reply has been sent. You can set it up as a trigger to auto-run every 15 minutes.

Also see: Email Auto-responder for Gmail


// Credit: https://gist.github.com/rboyd/5027691

function deleteLongEmails() {

    var WORDLIMIT = 100;
    
    // Process only new emails in inbox that have not been read and receive in the last day
    var threads = GmailApp.search("to:me is:unread in:inbox newer_than:1d");

    for (var i = 0; i < threads.length; i++) {

        // Ignore email conversations
        if (threads[i].getMessageCount() == 1) {

            var msg = threads[i].getMessages()[0];
            var word_count = msg.getPlainBody().split(' ').length;

            if (word_count > WORDLIMIT) {
                msg.reply("Your email is too long. Please resend after editing it to less than 100 words.");
                GmailApp.moveThreadToArchive(threads[i]);
            }
        }
    }
}

Categories
Code

Create Draft Messages with Attachments using Gmail API

The GmailApp service of Google Apps Script doesn’t support drafts creation but you use the Gmail API to programatically create drafts in your mailbox.

You can create basic HTML drafts or you can also create drafts with attachments as shown in the following example. It can also be extended to create duplicate drafts in Gmail – you fetch the source draft using the Gmail API itself, parse the MIME message, edit the text or html if required, and save it as a new draft.

// Written by Amit Agarwal
// Web: ctrlq.org
// Email: amit@labnol.org

function createDraftMessage() {

  var attachments = ["File_ID_1", "File_ID_2"];

  var message = {
    to: {
      name: "Google Scripts",
      email: "amit@labnol.org"
    },
    from: {
      name: "Amit Agarwal",
      email: "amit@labnol.org"
    },
    body: {
      text: "Mr hänn is schon lang nümme g'she.",
      html: "Mr hänn is schon lang nümme g'she."
    },
    subject: "ctrlq, tech à la carte",
    files: getAttachments_(attachments)
  };

  // Compose Gmail message and send immediately
  callGmailAPI_(message);

}


function callGmailAPI_(message) {

  var payload = createMimeMessage_(message);

  var response = UrlFetchApp.fetch(
    "https://www.googleapis.com/upload/gmail/v1/users/me/drafts?uploadType=media", {
      method: "POST",
      headers: {
        "Authorization": "Bearer " + ScriptApp.getOAuthToken(),
        "Content-Type": "message/rfc822",
      },
      muteHttpExceptions: true,
      payload: payload
    });

  Logger.log(response.getResponseCode());
  Logger.log(response.getContentText());

}

// UTF-8 characters in names and subject
function encode_(subject) {
  var enc_subject = Utilities.base64Encode(subject, Utilities.Charset.UTF_8);
  return '=?utf-8?B?' + enc_subject + '?=';
}

// Insert file attachments from Google Drive
function getAttachments_(ids) {
  var att = [];
  for (var i in ids) {
    var file = DriveApp.getFileById(ids[i]);
    att.push({
      mimeType: file.getMimeType(),
      fileName: file.getName(),
      bytes: Utilities.base64Encode(file.getBlob().getBytes())
    });
  }
  return att;
}

// Create a MIME message that complies with RFC 2822
function createMimeMessage_(msg) {

  var nl = "\n";
  var boundary = "__ctrlq_dot_org__";

  var mimeBody = [

    "MIME-Version: 1.0",
    "To: "      + encode_(msg.to.name) + "<" + msg.to.email + ">",
    "From: "    + encode_(msg.from.name) + "<" + msg.from.email + ">",
    "Subject: " + encode_(msg.subject), // takes care of accented characters

    "Content-Type: multipart/alternative; boundary=" + boundary + nl,
    "--" + boundary,

    "Content-Type: text/plain; charset=UTF-8",
    "Content-Transfer-Encoding: base64" + nl,
    Utilities.base64Encode(msg.body.text, Utilities.Charset.UTF_8) + nl,
    "--" + boundary,

    "Content-Type: text/html; charset=UTF-8",
    "Content-Transfer-Encoding: base64" + nl,
    Utilities.base64Encode(msg.body.html, Utilities.Charset.UTF_8) + nl

  ];

  for (var i = 0; i < msg.files.length; i++) {

    var attachment = [
      "--" + boundary,
      "Content-Type: " + msg.files[i].mimeType + '; name="' + msg.files[i].fileName + '"',
      'Content-Disposition: attachment; filename="' + msg.files[i].fileName + '"',
      "Content-Transfer-Encoding: base64" + nl,
      msg.files[i].bytes
    ];

    mimeBody.push(attachment.join(nl));

  }

  mimeBody.push("--" + boundary + "--");

  return mimeBody.join(nl);

}
Categories
Code

How to Use HTML5 Data Attributes with JavaScript

HTML5 supports data attributes that you can use to store extra information with any DOM elements. The name of these data attributes are prefixed with data-* (lowercase) and these can be easily parsed with the HTML5 dataset API.

For instance, if you an element has data attributes as data-name=”apple” and data-color=”red”, you can access them from JavaScript as elem.dataset.name and elem.dataset.color respectively. There’s no need to attach custom class names to attach properties to an HTML element.

The data-* attributes are supported in IE 10+ and all other browsers. Here’s a complete snippet.



  
    
    HTML5 data-* atributes
  
  
    
    
Categories
Code

Count Number of Pages in PDF with Google Script

The Google Documents API doesn’t offer a method to get the number of pages in a document. Dave Lam has an interesting workaround that will not only help you count the pages in a Google Document but of any PDF file that’s in your Google Drive.

The idea is to convert the Google Document into PDF and then parse the content of the PDF document as a text file using Regular Expressions.


/* Credit: https://code.google.com/p/google-apps-script-issues/issues/detail?id=1656 */

function countPDFPages() {

  var blob = DocumentApp.getActiveDocument().getAs("application/pdf");
  var data = blob.getDataAsString();

  var re = /Pages\/Count (\d+)/g;
  var match;
  var pages = 0;

  while (match = re.exec(data)) {

    var value = parseInt(match[1]);

    if (value > pages) {
      pages = value;
    }

  }

  Logger.log("Total PDF pages = " + pages);

  return pages;
}
Categories
Code

Caret (Arrow) Symbol in CSS

C^ret is an online tool that helps you create a caret (or an arrow symbol) in pure CSS. The caret can be pointing in any direction, the border width, the color can be changed and the code is generated as you move the slider.

Here’s a snippet for a downward pointing arrow. For more, see lugolabs.com/caret.

Categories
Code

HR with Centered Text

hr-text

This HTML/CSS snippet creates an HR element with text in the middle of the line. The text is supplied in the data-* attribute of the HR element.


You can divide with any text you like.

For instance this...


...this...


...even this!

body {
  text-align: center;
  background: #fcfcfa;
  color: #818078;
  font-family: Futura, sans-serif;
}

.container {
  max-width: 50%;  
  margin: 40px auto;
}

.hr-text {
  line-height: 1em;
  position: relative;
  outline: 0;
  border: 0;
  color: black;
  text-align: center;
  height: 1.5em;
  opacity: .5;
  &:before {
    content: '';
    // use the linear-gradient for the fading effect
    // use a solid background color for a solid bar
    background: linear-gradient(to right, transparent, #818078, transparent);
    position: absolute;
    left: 0;
    top: 50%;
    width: 100%;
    height: 1px;
  }
  &:after {
    content: attr(data-content);
    position: relative;
    display: inline-block;
    color: black;

    padding: 0 .5em;
    line-height: 1.5em;
    // this is really the only tricky part, you need to specify the background color of the container element...
    color: #818078;
    background-color: #fcfcfa;
  }
}
Categories
Code

Use OCR in Google Docs to Extract Text from Images

Google Docs supports OCR so you can upload a scanned PDF file or an image to Google Drive and it will extract the text from the file.

The OCR function can be called from Apps Script as well with the Drive REST API v2. You can specify the OCR parameter as true while downloading the file and it will perform OCR.

How to Use OCR with Google Docs

function doOCR() {
  
  var image = UrlFetchApp.fetch('http://img.labnol.org/logo.png').getBlob();

  var file = {
    title: 'OCR File',
    mimeType: 'image/png'
  };
  
  // OCR is supported for PDF and image formats
  file = Drive.Files.insert(file, image, {ocr: true});
  
  // Print the Google Document URL in the console
  Logger.log("File URL: %s", file.embedLink);
}

The function will fetch the web image and create a new Google Document in your Google Drive containing text and images extracted from the source file.

To use the function, you’ll need to enable the Drive API from the Google Developers Console for your Apps Script project.

Categories
Code

Center a DIV with Pure CSS

This CSS Snippet will place the DIV at the centre of a page. The div is centered both horizontally and vertically without requiring any jQuery or JavaScript. Credit: RawCode.io

Centre DIV with CSS



  
    
    Center DIV
    
  
  
    
Center DIV
Categories
Code

Insert Inline Image in a Google Document

The Apps Script snippet will insert an image at the current cursor position of the Google Document. You can either specify a web image (url starting with http), or if the image file is in your Google Drive, you can specify the full file URL (right-click a file in Drive and choose Get Link).

function insertImage() {

  // Insert a web image  
  var image = "http://img.labnol.org/logo.png";
  var blob = UrlFetchApp.fetch(image).getBlob();
  
  // Insert an image from Google Drive
  var image  = "https://drive.google.com/open?id=xyz";
  var fileID = image.match(/[\w\_\-]{25,}/).toString();
  var blob   = DriveApp.getFileById(fileID).getBlob();
  
  var doc = DocumentApp.getActiveDocument();
  var cursor = doc.getCursor();
  
  if (cursor) {    
    cursor.insertInlineImage(blob);
  } else {
    doc.getBody().insertImage(0, image);
  }
  
}

Categories
Code

Gmail Tracking with Google Analytics and Apps Script

This Google Script inserts the Google Analytics code in your outgoing email messages to help you track delivery and opens. When the email is opened, the tracking image (__utm.gif) – a 1×1 pixels transparent GIF file – is downloaded on the visitor’s computer and the visit gets logged as an event in Google Analytics.

You can use this technique to track email newsletters and other platforms where the standard JavaScript based Analytics code cannot be added.

Google Analytics tracking URL supports several parameters but this Apps Script function only uses only the essential ones that are required for logging an event (utmt is set as event and not page which is the default) into Google Analytics.

Also see: Tracking Email Clicks with Analytics


/* Written by Amit Agarwal */

function getTrackingGIF(account, email, subject) {

  var imgURL = "https://ssl.google-analytics.com/collect?"
    + "v=1&t=event"
    + "&tid=" + account
    + "&z="   + Math.round((new Date()).getTime()/1000).toString()
    + "&cid=" + Utilities.getUuid()
    + "&ec=" + encodeURIComponent("Email Open")
    + "&ea=" + encodeURIComponent(subject.replace(/'/g, ""))
    + "&el=" + encodeURIComponent(email);

  return "";

}

function sendMail(draftId, analyticsID) {

  var draft = GmailApp.getMessageById(draftId);
  
  var body = draft.getBody();
  body += getTrackingGIF(analyticsID, draft.getTo(), draft.getSubject());  
  
  GmailApp.sendEmail(draft.getTo(), draft.getSubject(), email, {
    htmlBody: email, cc: draft.getCc(), bcc: draft.getBcc()
  });          
}

Categories
Code

Create Trello Cards from a Google Form

Trello provides you a unique email address (like xyz@boards.trello.com) for any board in you account and any email message sent to this address is added as a new card to the Trello Board. @jezhou has written a Google Scripts that redirects Google Form submissions to a Trello using this email option.

When a Google Form is submitted, the onFormSubmit() event is triggered which then forwards the Google Form data to Trello via the GmailApp service. The subject is the title of the card while the email body goes in the description field. The script may be extended to forward Google Form entries to other services like WordPress, Evernote, Pocket, Tumblr, etc. since they too allow posting via Email.


// Credit: https://gist.github.com/jezhou/ac34cef8ce02aa051cfd/53a2b9605d99a13ebe090da5d4642be71b5e2e54

// Fire off this function in the script editor to enable.
function init() {

  var triggers = ScriptApp.getProjectTriggers();
  var form = FormApp.getActiveForm();
  
  // Delete all triggers before making a brand new one.
  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  
  // Set up a new trigger
  ScriptApp.newTrigger('submitToTrello')
           .forForm(form)
           .onFormSubmit()
           .create();
  
  Logger.log('Successful creation of new submitToTrello trigger.');
  
}

function submitToTrello(e) {
  
  var form = FormApp.getActiveForm();
  var latestItemResponses = form.getResponses().pop().getItemResponses();
 
  if (MailApp.getRemainingDailyQuota() > 0) {
    
    // Trello email address goes here
    var email = "ctrlq@boards.trello.com";
    
    // Subject line will be the title of the event on Trello card
    var subject = latestItemResponses[3].getResponse();
    
    // Intial empty body
    var body = "";
    
    // Loop through recent responses and format them into string
    latestItemResponses.forEach(function (value, index, array) {
      var formatted = Utilities.formatString("**%s**\n %s\n\n", value.getItem().getTitle(), value.getResponse());
      body = body.concat(formatted);
    });
        
    MailApp.sendEmail(email, subject, body);
  }

}