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

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

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

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

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

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

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

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

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

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
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
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

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

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

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

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

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

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

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

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

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

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

Fetch Google Search Results with the Site Search API

Google Site Search offers a JSON API allowing you to query Google Search results from your app. You can query web search results and image search and the results can be requested in both JSON and ATOM (XML) formats.

This Google Apps Script snippet shows you can use the JSON API to fetch Google Search results in a Google Spreadsheet programmatically. You can only make a limited number of API call with the free edition of Site Search (100 queries per day) but the Google Search Scraper is also an alternative.

function getGoogleSearchResults(q) {

  // Get the API key from Google's developer console
  // Get the CSE ID from google.com/cse

 var api = "https://www.googleapis.com/customsearch/v1?key="
              + KEY + "&cx=" + CSE + "&q=" + encodeURIComponent(q);

  try {

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

    if (response.getResponseCode() == 200) {

      var content = JSON.parse(response);
      
      // Did the search return any results?
      if (content.searchInformation.totalResults > 0) {

        var count = content.items.length;

        for (var i = 0; i < count; i++) {

          // Save the page title, description and hyperlink
          Logger.log(content.items[i].title);
          Logger.log(content.items[i].snippet);
          Logger.log(content.items[i].link);
        }
      }
    }
  } catch (f) {
    Logger.log(f.toString());
  }

}
Categories
Code

Post Google Forms Submissions to Slack

Andy Chase has published a Google Script that will let you automatically post Google Form submissions to a Slack channel. You need to place the script inside the script editor of your Google Forms editor and associate the onSubmit() method with the Form Submit trigger.

// replace this with your own Slack webhook URL
// https://crowdscores.slack.com/services
var webhookUrl = 'https://hooks.slack.com/services/****/****/****';

function onSubmit(e) {
  var response = e.response.getItemResponses();

  // Setup 2:
  // Modify the below to make the message you want. 
  // See: https://developers.google.com/apps-script/reference/forms/form-response
  var d = e.response.getRespondentEmail() + " | " + response[0].getResponse();

  var payload = {
    "payload": '{"text": "' + d + '"}'
  }

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

  UrlFetchApp.fetch(webhookUrl, options);
};

Also see: Email Notifications for Google Forms

Categories
Code

How to Send Tweets with Google Scripts and Twitter API

This example shows how you can setup your own Twitter Client using Google Apps Script and publish tweets programmatically from inside a Google Document, a spreadsheet or even a web app.

To get started, go to apps.twitter.com and create a new Twitter application. Switch to Keys and Access Tokens tab and generate your Access Token and Secret. This will help you send tweets from your Twitter account without manual authorization. Next in the Apps Script project, include the Twitter library (key: MKvHYYdYA4G5JJHj7hxIcoh8V4oX7X1M_) with the identifier set to Twitter.

function sendTweet(status) {
  
  status = status || "I found this snippet on @labnol's ctrlq.org";
  
  var twitterKeys= {
    TWITTER_CONSUMER_KEY: "<>",
    TWITTER_CONSUMER_SECRET: "<>",
    TWITTER_ACCESS_TOKEN: "<>",
    TWITTER_ACCESS_SECRET: "<>"    
  };
  
  var props = PropertiesService.getScriptProperties();
  
  props.setProperties(twitterKeys);
  
  var service = new Twitter.OAuth(props);
  
  if ( service.hasAccess() ) {
    
    var response = twit.sendTweet(status);
    
    if (response) {
      
      Logger.log("Tweet ID " + response.id_str);
      
    } else {
      
      // Tweet could not be sent
      // Go to View -> Logs to see the error message
      
    }
  }  
}
Categories
Code

Upload Files to Google Cloud Storage with Google Scripts

One of my recent project involved uploading files from Google Drive to a particular bucket in Google Cloud Storage. The front-end was created in a Google Spreadsheet where the full Google Drive path of the file is specified along with the destination bucket / folder in Google Cloud Storage.

The Google Apps Script would then automatically upload the files from Drive to Cloud Storage via a time-based trigger or can be run manually as well.

To get started, you need to go to the Google Developer’s console and enable the Google Cloud Storage API and the Google Cloud Storage JSON Data API. Also enable billing for your account to access Google Cloud Storage. Then create the API keys (OAuth 2, Web Application) with the redirect URL containing your Google Apps Script project key. The OAuth2 library for Apps Script should also be included in your project.

var params = {
  CLIENT_ID: 'xxx.apps.googleusercontent.com',
  CLIENT_SECRET: 'xyz',
  BUCKET_NAME: 'ctrlq-bucket',
  FILE_PATH: 'folder_name/filename',
  DRIVE_FILE: 'fileID'
};

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

function uploadFileToGCS() {

  var service = getService();
  if (!service.hasAccess()) {
    Logger.log("Please authorize %s", service.getAuthorizationUrl());
    return;
  }

  var blob = DriveApp.getFileById(params.DRIVE_FILE).getBlob();
  var bytes = blob.getBytes();

  var url = 'https://www.googleapis.com/upload/storage/v1/b/BUCKET/o?uploadType=media&name=FILE'
    .replace("BUCKET", params.BUCKET_NAME)
    .replace("FILE", encodeURIComponent(params.FILE_PATH));

  var response = UrlFetchApp.fetch(url, {
    method: "POST",
    contentLength: bytes.length,
    contentType: blob.getContentType(),
    payload: bytes,
    headers: {
      Authorization: 'Bearer ' + service.getAccessToken()
    }
  });

  var result = JSON.parse(response.getContentText());
  Logger.log(JSON.stringify(result, null, 2));
}

function getService() {
  return OAuth2.createService('ctrlq')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setClientId(params.CLIENT_ID)
    .setClientSecret(params.CLIENT_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('https://www.googleapis.com/auth/devstorage.read_write')
    .setParam('access_type', 'offline')
    .setParam('approval_prompt', 'force')
    .setParam('login_hint', Session.getActiveUser().getEmail());
}

function authCallback(request) {
  var service = getService();
  var authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Connected to Google Cloud Storage');
  } else {
    return HtmlService.createHtmlOutput('Access Denied');
  }
}

/* For support and customization, see www.ctrlq.org */
Categories
Code

Google Apps Script Library for Gmail API

The Gmail API offers extra functionality not available inside the Gmail service of Google Apps Script. For instance, the ability to purge Gmail folders is available in Apps Script but there’s no option to empty the trash. This can be easily done with the Gmail API. It also support draft creation that is used in Gmail Merge.

Spencer has published a wrapper library that makes it easy to use the Gmail API inside Google Apps Script.

/**
* Google Apps Script Library for the gmail API
*
* Credit: +SpencerEaston 
* Source: https://drive.google.com/folderview?id=0B_j9_-NbJQQDcUNEckk2WGhETms
* 
* OAuth2 Scopes
* https://mail.google.com/
* https://www.googleapis.com/auth/gmail.compose
* https://www.googleapis.com/auth/gmail.insert
* https://www.googleapis.com/auth/gmail.labels
* https://www.googleapis.com/auth/gmail.modify
* https://www.googleapis.com/auth/gmail.readonly
* https://www.googleapis.com/auth/gmail.send
*/

var BASEURL_="https://www.googleapis.com/gmail/v1/users/";
var tokenService_;

/*
* Stores the function passed that is invoked to get a OAuth2 token;
* @param {function} service The function used to get the OAuth2 token;
*
*/
function setTokenService(service){
  tokenService_ = service;
}

/*
* Returns an OAuth2 token from your TokenService as a test
* @return {string} An OAuth2 token
*
*/
function testTokenService(){
 return tokenService_();
}

/**
 * Performs a Fetch
 * @param {string} url The endpoint for the URL with parameters
 * @param {Object.} options Options to override default fetch options
 * @returns {Object.} the fetch results
 * @private
 */
function CALL_(path,options){
  var fetchOptions = {method:"",muteHttpExceptions:true, contentType:"application/json", headers:{Authorization:"Bearer "+tokenService_()}}
  var url = BASEURL_ + path;
  
  for(option in options){
    fetchOptions[option] = options[option];
  }
  
  var response = UrlFetchApp.fetch(url, fetchOptions)
  if(response.getResponseCode() != 200){
    throw new Error(response.getContentText())
  }else{
    return JSON.parse(response.getContentText());
  }
}

/**
 * Performs a Fetch and accumulation using pageToken parameter of the returned results
 * @param {string} url The endpoint for the URL with parameters
 * @param {Object.} options Options to override default fetch options
 * @param {string} returnParamPath The path of the parameter to be accumulated
 * @returns {Array.Object.} An array of objects
 * @private
 */
function CALLPAGE_(path,options, returnParamPath){
  var fetchOptions = {method:"",muteHttpExceptions:true, contentType:"application/json", headers:{Authorization:"Bearer "+tokenService_()}}
  for(option in options){
    fetchOptions[option] = options[option];
  }
  var url = BASEURL_ + path;
  var returnArray = [];
  var nextPageToken;
  do{
    if(nextPageToken){
      url += "?pageToken=" + nextPageToken;
    }
    var results = UrlFetchApp.fetch(url, fetchOptions);
    if(results.getResponseCode() != 200){
      throw new Error(results.getContentText());
    }else{
      var resp = JSON.parse(results.getContentText())
      nextPageToken = resp.nextPageToken;
      returnArray  = returnArray.concat(resp[returnParamPath])
    }
  }while(nextPageToken);
  return returnArray;
}

/**
 * Builds a complete URL from a base URL and a map of URL parameters.
 * @param {string} url The base URL.
 * @param {Object.} params The URL parameters and values.
 * @returns {string} The complete URL.
 * @private
 */
function buildUrl_(url, params) {
  var params = params || {}; //allow for NULL options
  var paramString = Object.keys(params).map(function(key) {
    return encodeURIComponent(key) + '=' + encodeURIComponent(params[key]);
  }).join('&');
  return url + (url.indexOf('?') >= 0 ? '&' : '?') + paramString;
}

/**
* Gets the current user's Gmail profile.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ProfileResource object
*/
function usersGetProfile(userId,options){
  var path = buildUrl_(""+userId+"/profile",options);
  var callOptions = {method:"GET"};
  var ProfileResource = CALL_(path,callOptions);
  return ProfileResource;
}

/**
* Stop receiving push notifications for the given user mailbox.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} options Keypair of all optional parameters for this call
*/
function usersStop(userId,options){
  var path = buildUrl_(""+userId+"/stop",options);
  var callOptions = {method:"POST"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Set up or update a push notification watch on the given user mailbox.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} WatchRequestResource An object containing the WatchRequestResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned WatchResponseResource object
*/
function usersWatch(userId,WatchRequestResource,options){
  var path = buildUrl_(""+userId+"/watch",options);
  var callOptions = {method:"POST",payload:JSON.stringify(WatchRequestResource)};
  var WatchResponseResource = CALL_(path,callOptions);
  return WatchResponseResource;
}

/**
* Creates a new draft with the DRAFT label.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} DraftResource An object containing the DraftResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned DraftResource object
*/
function usersDraftsCreate(userId,DraftResource,options){
  var path = buildUrl_(""+userId+"/drafts",options);
  var callOptions = {method:"POST",payload:JSON.stringify(DraftResource)};
  var DraftResource = CALL_(path,callOptions);
  return DraftResource;
}

/**
* Immediately and permanently deletes the specified draft. Does not simply trash it.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the draft to delete.
* @param {object} options Keypair of all optional parameters for this call
*/
function usersDraftsDelete(userId,id,options){
  var path = buildUrl_(""+userId+"/drafts/"+id,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets the specified draft.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the draft to retrieve.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned DraftResource object
*/
function usersDraftsGet(userId,id,options){
  var path = buildUrl_(""+userId+"/drafts/"+id,options);
  var callOptions = {method:"GET"};
  var DraftResource = CALL_(path,callOptions);
  return DraftResource;
}

/**
* Lists the drafts in the user's mailbox.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ListDraftsResponseResource object
*/
function usersDraftsList(userId,options){
  var path = buildUrl_(""+userId+"/drafts",options);
  var callOptions = {method:"GET"};
  var ListDraftsResponseItems = CALLPAGE_(path,callOptions,"items");
  return ListDraftsResponseItems;
}

/**
* Sends the specified, existing draft to the recipients in the To, Cc, and Bcc headers.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} DraftResource An object containing the DraftResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned MessageResource object
*/
function usersDraftsSend(userId,DraftResource,options){
  var path = buildUrl_(""+userId+"/drafts/send",options);
  var callOptions = {method:"POST",payload:JSON.stringify(DraftResource)};
  var MessageResource = CALL_(path,callOptions);
  return MessageResource;
}

/**
* Replaces a draft's content.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the draft to update.
* @param {object} DraftResource An object containing the DraftResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned DraftResource object
*/
function usersDraftsUpdate(userId,id,DraftResource,options){
  var path = buildUrl_(""+userId+"/drafts/"+id,options);
  var callOptions = {method:"PUT",payload:JSON.stringify(DraftResource)};
  var DraftResource = CALL_(path,callOptions);
  return DraftResource;
}

/**
* Lists the history of all changes to the given mailbox. History results are returned in chronological order (increasing historyId).
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ListHistoryResponseResource object
*/
function usersHistoryList(userId,options){
  var path = buildUrl_(""+userId+"/history",options);
  var callOptions = {method:"GET"};
  var ListHistoryResponseItems = CALLPAGE_(path,callOptions,"items");
  return ListHistoryResponseItems;
}

/**
* Creates a new label.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} LabelResource An object containing the LabelResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned LabelResource object
*/
function usersLabelsCreate(userId,LabelResource,options){
  var path = buildUrl_(""+userId+"/labels",options);
  var callOptions = {method:"POST",payload:JSON.stringify(LabelResource)};
  var LabelResource = CALL_(path,callOptions);
  return LabelResource;
}

/**
* Immediately and permanently deletes the specified label and removes it from any messages and threads that it is applied to.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the label to delete.
* @param {object} options Keypair of all optional parameters for this call
*/
function usersLabelsDelete(userId,id,options){
  var path = buildUrl_(""+userId+"/labels/"+id,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets the specified label.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the label to retrieve.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned LabelResource object
*/
function usersLabelsGet(userId,id,options){
  var path = buildUrl_(""+userId+"/labels/"+id,options);
  var callOptions = {method:"GET"};
  var LabelResource = CALL_(path,callOptions);
  return LabelResource;
}

/**
* Lists all labels in the user's mailbox.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ListLabelsResponseResource object
*/
function usersLabelsList(userId,options){
  var path = buildUrl_(""+userId+"/labels",options);
  var callOptions = {method:"GET"};
  var ListLabelsResponseResource = CALL_(path,callOptions);
  return ListLabelsResponseResource;
}

/**
* Updates the specified label. This method supports patch semantics.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the label to update.
* @param {object} LabelResource An object containing the LabelResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned LabelResource object
*/
function usersLabelsPatch(userId,id,LabelResource,options){
  var path = buildUrl_(""+userId+"/labels/"+id,options);
  var callOptions = {method:"PATCH",payload:JSON.stringify(LabelResource)};
  var LabelResource = CALL_(path,callOptions);
  return LabelResource;
}

/**
* Updates the specified label.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the label to update.
* @param {object} LabelResource An object containing the LabelResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned LabelResource object
*/
function usersLabelsUpdate(userId,id,LabelResource,options){
  var path = buildUrl_(""+userId+"/labels/"+id,options);
  var callOptions = {method:"PUT",payload:JSON.stringify(LabelResource)};
  var LabelResource = CALL_(path,callOptions);
  return LabelResource;
}

/**
* Immediately and permanently deletes the specified message. This operation cannot be undone. Prefer messages.trash instead.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the message to delete.
* @param {object} options Keypair of all optional parameters for this call
*/
function usersMessagesDelete(userId,id,options){
  var path = buildUrl_(""+userId+"/messages/"+id,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets the specified message.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the message to retrieve.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned MessageResource object
*/
function usersMessagesGet(userId,id,options){
  var path = buildUrl_(""+userId+"/messages/"+id,options);
  var callOptions = {method:"GET"};
  var MessageResource = CALL_(path,callOptions);
  return MessageResource;
}

/**
* Imports a message into only this user's mailbox, with standard email delivery scanning and classification similar to receiving via SMTP. Does not send a message.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} MessageResource An object containing the MessageResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned MessageResource object
*/
function usersMessagesImport(userId,MessageResource,options){
  var path = buildUrl_(""+userId+"/messages/import",options);
  var callOptions = {method:"POST",payload:JSON.stringify(MessageResource)};
  var MessageResource = CALL_(path,callOptions);
  return MessageResource;
}

/**
* Directly inserts a message into only this user's mailbox similar to IMAP APPEND, bypassing most scanning and classification. Does not send a message.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} MessageResource An object containing the MessageResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned MessageResource object
*/
function usersMessagesInsert(userId,MessageResource,options){
  var path = buildUrl_(""+userId+"/messages",options);
  var callOptions = {method:"POST",payload:JSON.stringify(MessageResource)};
  var MessageResource = CALL_(path,callOptions);
  return MessageResource;
}

/**
* Lists the messages in the user's mailbox.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ListMessagesResponseResource object
*/
function usersMessagesList(userId,options){
  var path = buildUrl_(""+userId+"/messages",options);
  var callOptions = {method:"GET"};
  var ListMessagesResponseItems = CALLPAGE_(path,callOptions,"items");
  return ListMessagesResponseItems;
}

/**
* Modifies the labels on the specified message.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the message to modify.
* @param {object} ModifyMessageRequestResource An object containing the ModifyMessageRequestResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned MessageResource object
*/
function usersMessagesModify(userId,id,ModifyMessageRequestResource,options){
  var path = buildUrl_(""+userId+"/messages/"+id+"/modify",options);
  var callOptions = {method:"POST",payload:JSON.stringify(ModifyMessageRequestResource)};
  var MessageResource = CALL_(path,callOptions);
  return MessageResource;
}

/**
* Sends the specified message to the recipients in the To, Cc, and Bcc headers.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} MessageResource An object containing the MessageResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned MessageResource object
*/
function usersMessagesSend(userId,MessageResource,options){
  var path = buildUrl_(""+userId+"/messages/send",options);
  var callOptions = {method:"POST",payload:JSON.stringify(MessageResource)};
  var MessageResource = CALL_(path,callOptions);
  return MessageResource;
}

/**
* Moves the specified message to the trash.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the message to Trash.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned MessageResource object
*/
function usersMessagesTrash(userId,id,options){
  var path = buildUrl_(""+userId+"/messages/"+id+"/trash",options);
  var callOptions = {method:"POST"};
  var MessageResource = CALL_(path,callOptions);
  return MessageResource;
}

/**
* Removes the specified message from the trash.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the message to remove from Trash.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned MessageResource object
*/
function usersMessagesUntrash(userId,id,options){
  var path = buildUrl_(""+userId+"/messages/"+id+"/untrash",options);
  var callOptions = {method:"POST"};
  var MessageResource = CALL_(path,callOptions);
  return MessageResource;
}

/**
* Gets the specified message attachment.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} messageId The ID of the message containing the attachment.
* @param {string} id The ID of the attachment.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned MessagePartBodyResource object
*/
function usersMessagesAttachmentsGet(userId,messageId,id,options){
  var path = buildUrl_(""+userId+"/messages/"+messageId+"/attachments/"+id,options);
  var callOptions = {method:"GET"};
  var MessagePartBodyResource = CALL_(path,callOptions);
  return MessagePartBodyResource;
}

/**
* Immediately and permanently deletes the specified thread. This operation cannot be undone. Prefer threads.trash instead.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id ID of the Thread to delete.
* @param {object} options Keypair of all optional parameters for this call
*/
function usersThreadsDelete(userId,id,options){
  var path = buildUrl_(""+userId+"/threads/"+id,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets the specified thread.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the thread to retrieve.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ThreadResource object
*/
function usersThreadsGet(userId,id,options){
  var path = buildUrl_(""+userId+"/threads/"+id,options);
  var callOptions = {method:"GET"};
  var ThreadResource = CALL_(path,callOptions);
  return ThreadResource;
}

/**
* Lists the threads in the user's mailbox.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ListThreadsResponseResource object
*/
function usersThreadsList(userId,options){
  var path = buildUrl_(""+userId+"/threads",options);
  var callOptions = {method:"GET"};
  var ListThreadsResponseItems = CALLPAGE_(path,callOptions,"items");
  return ListThreadsResponseItems;
}

/**
* Modifies the labels applied to the thread. This applies to all messages in the thread.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the thread to modify.
* @param {object} ModifyThreadRequestResource An object containing the ModifyThreadRequestResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ThreadResource object
*/
function usersThreadsModify(userId,id,ModifyThreadRequestResource,options){
  var path = buildUrl_(""+userId+"/threads/"+id+"/modify",options);
  var callOptions = {method:"POST",payload:JSON.stringify(ModifyThreadRequestResource)};
  var ThreadResource = CALL_(path,callOptions);
  return ThreadResource;
}

/**
* Moves the specified thread to the trash.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the thread to Trash.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ThreadResource object
*/
function usersThreadsTrash(userId,id,options){
  var path = buildUrl_(""+userId+"/threads/"+id+"/trash",options);
  var callOptions = {method:"POST"};
  var ThreadResource = CALL_(path,callOptions);
  return ThreadResource;
}

/**
* Removes the specified thread from the trash.
*
* @param {string} userId The user's email address. The special value me can be used to indicate the authenticated user.
* @param {string} id The ID of the thread to remove from Trash.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ThreadResource object
*/
function usersThreadsUntrash(userId,id,options){
  var path = buildUrl_(""+userId+"/threads/"+id+"/untrash",options);
  var callOptions = {method:"POST"};
  var ThreadResource = CALL_(path,callOptions);
  return ThreadResource;
}

Categories
Code

Google Apps Script Library for Google Drive API

The advanced Google Drive API offers access to functionality that is not available with the Drive API inside Google Apps Script. For instance, the Drive API offers Revisions that helps you monitor file changes. Google has published a library for the Drive API that makes it easy to use the API inside Google Scripts.

Spencer has published a wrapper library that makes it easy to use the advanced Google Drive API inside Google Apps Script.

/**
* Google Apps Script Library for the drive API
*
* Credit: +SpencerEaston 
* Source: https://drive.google.com/folderview?id=0B_j9_-NbJQQDcUNEckk2WGhETms 
* 
* OAuth2 Scopes
* https://www.googleapis.com/auth/drive
* https://www.googleapis.com/auth/drive.appdata
* https://www.googleapis.com/auth/drive.apps.readonly
* https://www.googleapis.com/auth/drive.file
* https://www.googleapis.com/auth/drive.metadata
* https://www.googleapis.com/auth/drive.metadata.readonly
* https://www.googleapis.com/auth/drive.photos.readonly
* https://www.googleapis.com/auth/drive.readonly
* https://www.googleapis.com/auth/drive.scripts
*/

var BASEURL_="https://www.googleapis.com/drive/v2/";
var tokenService_;

/*
* Stores the function passed that is invoked to get a OAuth2 token;
* @param {function} service The function used to get the OAuth2 token;
*
*/
function setTokenService(service){
  tokenService_ = service;
}

/*
* Returns an OAuth2 token from your TokenService as a test
* @return {string} An OAuth2 token
*
*/
function testTokenService(){
 return tokenService_();
}

/**
 * Performs a Fetch
 * @param {string} url The endpoint for the URL with parameters
 * @param {Object.} options Options to override default fetch options
 * @returns {Object.} the fetch results
 * @private
 */
function CALL_(path,options){
  var fetchOptions = {method:"",muteHttpExceptions:true, contentType:"application/json", headers:{Authorization:"Bearer "+tokenService_()}}
  var url = BASEURL_ + path;
  
  for(option in options){
    fetchOptions[option] = options[option];
  }
  
  var response = UrlFetchApp.fetch(url, fetchOptions)
  if(response.getResponseCode() != 200){
    throw new Error(response.getContentText())
  }else{
    return JSON.parse(response.getContentText());
  }
}

/**
 * Performs a Fetch and accumulation using pageToken parameter of the returned results
 * @param {string} url The endpoint for the URL with parameters
 * @param {Object.} options Options to override default fetch options
 * @param {string} returnParamPath The path of the parameter to be accumulated
 * @returns {Array.Object.} An array of objects
 * @private
 */
function CALLPAGE_(path,options, returnParamPath){
  var fetchOptions = {method:"",muteHttpExceptions:true, contentType:"application/json", headers:{Authorization:"Bearer "+tokenService_()}}
  for(option in options){
    fetchOptions[option] = options[option];
  }
  var url = BASEURL_ + path;
  var returnArray = [];
  var nextPageToken;
  do{
    if(nextPageToken){
      url += "?pageToken=" + nextPageToken;
    }
    var results = UrlFetchApp.fetch(url, fetchOptions);
    if(results.getResponseCode() != 200){
      throw new Error(results.getContentText());
    }else{
      var resp = JSON.parse(results.getContentText())
      nextPageToken = resp.nextPageToken;
      returnArray  = returnArray.concat(resp[returnParamPath])
    }
  }while(nextPageToken);
  return returnArray;
}

/**
 * Builds a complete URL from a base URL and a map of URL parameters.
 * @param {string} url The base URL.
 * @param {Object.} params The URL parameters and values.
 * @returns {string} The complete URL.
 * @private
 */
function buildUrl_(url, params) {
  var params = params || {}; //allow for NULL options
  var paramString = Object.keys(params).map(function(key) {
    return encodeURIComponent(key) + '=' + encodeURIComponent(params[key]);
  }).join('&');
  return url + (url.indexOf('?') >= 0 ? '&' : '?') + paramString;
}

/**
* Gets the information about the current user along with Drive API settings
*
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned AboutResource object
*/
function aboutGet(options){
  var path = buildUrl_("about",options);
  var callOptions = {method:"GET"};
  var AboutResource = CALL_(path,callOptions);
  return AboutResource;
}

/**
* Gets a specific app.
*
* @param {string} appId The ID of the app.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned AppResource object
*/
function appsGet(appId,options){
  var path = buildUrl_("apps/"+appId,options);
  var callOptions = {method:"GET"};
  var AppResource = CALL_(path,callOptions);
  return AppResource;
}

/**
* Lists a user's installed apps.
*
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned AppListResource object
*/
function appsList(options){
  var path = buildUrl_("apps",options);
  var callOptions = {method:"GET"};
  var AppListResource = CALL_(path,callOptions);
  return AppListResource;
}

/**
* Gets a specific change.
*
* @param {string} changeId The ID of the change.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ChangeResource object
*/
function changesGet(changeId,options){
  var path = buildUrl_("changes/"+changeId,options);
  var callOptions = {method:"GET"};
  var ChangeResource = CALL_(path,callOptions);
  return ChangeResource;
}

/**
* Lists the changes for a user.
*
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ChangeListResource object
*/
function changesList(options){
  var path = buildUrl_("changes",options);
  var callOptions = {method:"GET"};
  var ChangeListItems = CALLPAGE_(path,callOptions,"items");
  return ChangeListItems;
}

/**
* Subscribe to changes for a user.
*
* @param {object} ChannelResource An object containing the ChannelResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ChannelResource object
*/
function changesWatch(ChannelResource,options){
  var path = buildUrl_("changes/watch",options);
  var callOptions = {method:"POST",payload:JSON.stringify(ChannelResource)};
  var ChannelItems = CALLPAGE_(path,callOptions,"items");
  return ChannelItems;
}

/**
* Stop watching resources through this channel
*
* @param {object} ChannelResource An object containing the ChannelResource for this method
* @param {object} options Keypair of all optional parameters for this call
*/
function channelsStop(ChannelResource,options){
  var path = buildUrl_("channels/stop",options);
  var callOptions = {method:"POST",payload:JSON.stringify(ChannelResource)};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Removes a child from a folder.
*
* @param {string} folderId The ID of the folder.
* @param {string} childId The ID of the child.
* @param {object} options Keypair of all optional parameters for this call
*/
function childrenDelete(folderId,childId,options){
  var path = buildUrl_("files/"+folderId+"/children/"+childId,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets a specific child reference.
*
* @param {string} folderId The ID of the folder.
* @param {string} childId The ID of the child.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ChildReferenceResource object
*/
function childrenGet(folderId,childId,options){
  var path = buildUrl_("files/"+folderId+"/children/"+childId,options);
  var callOptions = {method:"GET"};
  var ChildReferenceResource = CALL_(path,callOptions);
  return ChildReferenceResource;
}

/**
* Inserts a file into a folder.
*
* @param {string} folderId The ID of the folder.
* @param {object} ChildReferenceResource An object containing the ChildReferenceResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ChildReferenceResource object
*/
function childrenInsert(folderId,ChildReferenceResource,options){
  var path = buildUrl_("files/"+folderId+"/children",options);
  var callOptions = {method:"POST",payload:JSON.stringify(ChildReferenceResource)};
  var ChildReferenceResource = CALL_(path,callOptions);
  return ChildReferenceResource;
}

/**
* Lists a folder's children.
*
* @param {string} folderId The ID of the folder.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ChildListResource object
*/
function childrenList(folderId,options){
  var path = buildUrl_("files/"+folderId+"/children",options);
  var callOptions = {method:"GET"};
  var ChildListItems = CALLPAGE_(path,callOptions,"items");
  return ChildListItems;
}

/**
* Deletes a comment.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {object} options Keypair of all optional parameters for this call
*/
function commentsDelete(fileId,commentId,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets a comment by ID.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentResource object
*/
function commentsGet(fileId,commentId,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId,options);
  var callOptions = {method:"GET"};
  var CommentResource = CALL_(path,callOptions);
  return CommentResource;
}

/**
* Creates a new comment on the given file.
*
* @param {string} fileId The ID of the file.
* @param {object} CommentResource An object containing the CommentResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentResource object
*/
function commentsInsert(fileId,CommentResource,options){
  var path = buildUrl_("files/"+fileId+"/comments",options);
  var callOptions = {method:"POST",payload:JSON.stringify(CommentResource)};
  var CommentResource = CALL_(path,callOptions);
  return CommentResource;
}

/**
* Lists a file's comments.
*
* @param {string} fileId The ID of the file.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentListResource object
*/
function commentsList(fileId,options){
  var path = buildUrl_("files/"+fileId+"/comments",options);
  var callOptions = {method:"GET"};
  var CommentListItems = CALLPAGE_(path,callOptions,"items");
  return CommentListItems;
}

/**
* Updates an existing comment. This method supports patch semantics.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {object} CommentResource An object containing the CommentResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentResource object
*/
function commentsPatch(fileId,commentId,CommentResource,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId,options);
  var callOptions = {method:"PATCH",payload:JSON.stringify(CommentResource)};
  var CommentResource = CALL_(path,callOptions);
  return CommentResource;
}

/**
* Updates an existing comment.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {object} CommentResource An object containing the CommentResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentResource object
*/
function commentsUpdate(fileId,commentId,CommentResource,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId,options);
  var callOptions = {method:"PUT",payload:JSON.stringify(CommentResource)};
  var CommentResource = CALL_(path,callOptions);
  return CommentResource;
}

/**
* Creates a copy of the specified file.
*
* @param {string} fileId The ID of the file to copy.
* @param {object} FileResource An object containing the FileResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned FileResource object
*/
function filesCopy(fileId,FileResource,options){
  var path = buildUrl_("files/"+fileId+"/copy",options);
  var callOptions = {method:"POST",payload:JSON.stringify(FileResource)};
  var FileResource = CALL_(path,callOptions);
  return FileResource;
}

/**
* Permanently deletes a file by ID. Skips the trash. The currently authenticated user must own the file.
*
* @param {string} fileId The ID of the file to delete.
* @param {object} options Keypair of all optional parameters for this call
*/
function filesDelete(fileId,options){
  var path = buildUrl_("files/"+fileId,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Permanently deletes all of the user's trashed files.
*
* @param {object} options Keypair of all optional parameters for this call
*/
function filesEmptyTrash(options){
  var path = buildUrl_("files/trash",options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Generates a set of file IDs which can be provided in insert requests.
*
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned GeneratedIdsResource object
*/
function filesGenerateIds(options){
  var path = buildUrl_("files/generateIds",options);
  var callOptions = {method:"GET"};
  var GeneratedIdsResource = CALL_(path,callOptions);
  return GeneratedIdsResource;
}

/**
* Gets a file's metadata by ID.
*
* @param {string} fileId The ID for the file in question.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned FileResource object
*/
function filesGet(fileId,options){
  var path = buildUrl_("files/"+fileId,options);
  var callOptions = {method:"GET"};
  var FileResource = CALL_(path,callOptions);
  return FileResource;
}

/**
* Insert a new file.
*
* @param {object} FileResource An object containing the FileResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned FileResource object
*/
function filesInsert(FileResource,options){
  var path = buildUrl_("files",options);
  var callOptions = {method:"POST",payload:JSON.stringify(FileResource)};
  var FileResource = CALL_(path,callOptions);
  return FileResource;
}

/**
* Lists the user's files.
*
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned FileListResource object
*/
function filesList(options){
  var path = buildUrl_("files",options);
  var callOptions = {method:"GET"};
  var FileListItems = CALLPAGE_(path,callOptions,"items");
  return FileListItems;
}

/**
* Updates file metadata and/or content. This method supports patch semantics.
*
* @param {string} fileId The ID of the file to update.
* @param {object} FileResource An object containing the FileResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned FileResource object
*/
function filesPatch(fileId,FileResource,options){
  var path = buildUrl_("files/"+fileId,options);
  var callOptions = {method:"PATCH",payload:JSON.stringify(FileResource)};
  var FileResource = CALL_(path,callOptions);
  return FileResource;
}

/**
* Set the file's updated time to the current server time.
*
* @param {string} fileId The ID of the file to update.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned FileResource object
*/
function filesTouch(fileId,options){
  var path = buildUrl_("files/"+fileId+"/touch",options);
  var callOptions = {method:"POST"};
  var FileResource = CALL_(path,callOptions);
  return FileResource;
}

/**
* Moves a file to the trash. The currently authenticated user must own the file.
*
* @param {string} fileId The ID of the file to trash.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned FileResource object
*/
function filesTrash(fileId,options){
  var path = buildUrl_("files/"+fileId+"/trash",options);
  var callOptions = {method:"POST"};
  var FileResource = CALL_(path,callOptions);
  return FileResource;
}

/**
* Restores a file from the trash.
*
* @param {string} fileId The ID of the file to untrash.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned FileResource object
*/
function filesUntrash(fileId,options){
  var path = buildUrl_("files/"+fileId+"/untrash",options);
  var callOptions = {method:"POST"};
  var FileResource = CALL_(path,callOptions);
  return FileResource;
}

/**
* Updates file metadata and/or content.
*
* @param {string} fileId The ID of the file to update.
* @param {object} FileResource An object containing the FileResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned FileResource object
*/
function filesUpdate(fileId,FileResource,options){
  var path = buildUrl_("files/"+fileId,options);
  var callOptions = {method:"PUT",payload:JSON.stringify(FileResource)};
  var FileResource = CALL_(path,callOptions);
  return FileResource;
}

/**
* Subscribe to changes on a file
*
* @param {string} fileId The ID for the file in question.
* @param {object} ChannelResource An object containing the ChannelResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ChannelResource object
*/
function filesWatch(fileId,ChannelResource,options){
  var path = buildUrl_("files/"+fileId+"/watch",options);
  var callOptions = {method:"POST",payload:JSON.stringify(ChannelResource)};
  var ChannelResource = CALL_(path,callOptions);
  return ChannelResource;
}

/**
* Removes a parent from a file.
*
* @param {string} fileId The ID of the file.
* @param {string} parentId The ID of the parent.
* @param {object} options Keypair of all optional parameters for this call
*/
function parentsDelete(fileId,parentId,options){
  var path = buildUrl_("files/"+fileId+"/parents/"+parentId,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets a specific parent reference.
*
* @param {string} fileId The ID of the file.
* @param {string} parentId The ID of the parent.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ParentReferenceResource object
*/
function parentsGet(fileId,parentId,options){
  var path = buildUrl_("files/"+fileId+"/parents/"+parentId,options);
  var callOptions = {method:"GET"};
  var ParentReferenceResource = CALL_(path,callOptions);
  return ParentReferenceResource;
}

/**
* Adds a parent folder for a file.
*
* @param {string} fileId The ID of the file.
* @param {object} ParentReferenceResource An object containing the ParentReferenceResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ParentReferenceResource object
*/
function parentsInsert(fileId,ParentReferenceResource,options){
  var path = buildUrl_("files/"+fileId+"/parents",options);
  var callOptions = {method:"POST",payload:JSON.stringify(ParentReferenceResource)};
  var ParentReferenceResource = CALL_(path,callOptions);
  return ParentReferenceResource;
}

/**
* Lists a file's parents.
*
* @param {string} fileId The ID of the file.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned ParentListResource object
*/
function parentsList(fileId,options){
  var path = buildUrl_("files/"+fileId+"/parents",options);
  var callOptions = {method:"GET"};
  var ParentListResource = CALL_(path,callOptions);
  return ParentListResource;
}

/**
* Deletes a permission from a file.
*
* @param {string} fileId The ID for the file.
* @param {string} permissionId The ID for the permission.
* @param {object} options Keypair of all optional parameters for this call
*/
function permissionsDelete(fileId,permissionId,options){
  var path = buildUrl_("files/"+fileId+"/permissions/"+permissionId,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets a permission by ID.
*
* @param {string} fileId The ID for the file.
* @param {string} permissionId The ID for the permission.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PermissionResource object
*/
function permissionsGet(fileId,permissionId,options){
  var path = buildUrl_("files/"+fileId+"/permissions/"+permissionId,options);
  var callOptions = {method:"GET"};
  var PermissionResource = CALL_(path,callOptions);
  return PermissionResource;
}

/**
* Returns the permission ID for an email address.
*
* @param {string} email The email address for which to return a permission ID
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PermissionIdResource object
*/
function permissionsGetIdForEmail(email,options){
  var path = buildUrl_("permissionIds/"+email,options);
  var callOptions = {method:"GET"};
  var PermissionIdResource = CALL_(path,callOptions);
  return PermissionIdResource;
}

/**
* Inserts a permission for a file.
*
* @param {string} fileId The ID for the file.
* @param {object} PermissionResource An object containing the PermissionResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PermissionResource object
*/
function permissionsInsert(fileId,PermissionResource,options){
  var path = buildUrl_("files/"+fileId+"/permissions",options);
  var callOptions = {method:"POST",payload:JSON.stringify(PermissionResource)};
  var PermissionResource = CALL_(path,callOptions);
  return PermissionResource;
}

/**
* Lists a file's permissions.
*
* @param {string} fileId The ID for the file.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PermissionListResource object
*/
function permissionsList(fileId,options){
  var path = buildUrl_("files/"+fileId+"/permissions",options);
  var callOptions = {method:"GET"};
  var PermissionListResource = CALL_(path,callOptions);
  return PermissionListResource;
}

/**
* Updates a permission using patch semantics.
*
* @param {string} fileId The ID for the file.
* @param {string} permissionId The ID for the permission.
* @param {object} PermissionResource An object containing the PermissionResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PermissionResource object
*/
function permissionsPatch(fileId,permissionId,PermissionResource,options){
  var path = buildUrl_("files/"+fileId+"/permissions/"+permissionId,options);
  var callOptions = {method:"PATCH",payload:JSON.stringify(PermissionResource)};
  var PermissionResource = CALL_(path,callOptions);
  return PermissionResource;
}

/**
* Updates a permission.
*
* @param {string} fileId The ID for the file.
* @param {string} permissionId The ID for the permission.
* @param {object} PermissionResource An object containing the PermissionResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PermissionResource object
*/
function permissionsUpdate(fileId,permissionId,PermissionResource,options){
  var path = buildUrl_("files/"+fileId+"/permissions/"+permissionId,options);
  var callOptions = {method:"PUT",payload:JSON.stringify(PermissionResource)};
  var PermissionResource = CALL_(path,callOptions);
  return PermissionResource;
}

/**
* Deletes a property.
*
* @param {string} fileId The ID of the file.
* @param {string} propertyKey The key of the property.
* @param {object} options Keypair of all optional parameters for this call
*/
function propertiesDelete(fileId,propertyKey,options){
  var path = buildUrl_("files/"+fileId+"/properties/"+propertyKey,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets a property by its key.
*
* @param {string} fileId The ID of the file.
* @param {string} propertyKey The key of the property.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PropertyResource object
*/
function propertiesGet(fileId,propertyKey,options){
  var path = buildUrl_("files/"+fileId+"/properties/"+propertyKey,options);
  var callOptions = {method:"GET"};
  var PropertyResource = CALL_(path,callOptions);
  return PropertyResource;
}

/**
* Adds a property to a file.
*
* @param {string} fileId The ID of the file.
* @param {object} PropertyResource An object containing the PropertyResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PropertyResource object
*/
function propertiesInsert(fileId,PropertyResource,options){
  var path = buildUrl_("files/"+fileId+"/properties",options);
  var callOptions = {method:"POST",payload:JSON.stringify(PropertyResource)};
  var PropertyResource = CALL_(path,callOptions);
  return PropertyResource;
}

/**
* Lists a file's properties.
*
* @param {string} fileId The ID of the file.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PropertyListResource object
*/
function propertiesList(fileId,options){
  var path = buildUrl_("files/"+fileId+"/properties",options);
  var callOptions = {method:"GET"};
  var PropertyListResource = CALL_(path,callOptions);
  return PropertyListResource;
}

/**
* Updates a property. This method supports patch semantics.
*
* @param {string} fileId The ID of the file.
* @param {string} propertyKey The key of the property.
* @param {object} PropertyResource An object containing the PropertyResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PropertyResource object
*/
function propertiesPatch(fileId,propertyKey,PropertyResource,options){
  var path = buildUrl_("files/"+fileId+"/properties/"+propertyKey,options);
  var callOptions = {method:"PATCH",payload:JSON.stringify(PropertyResource)};
  var PropertyResource = CALL_(path,callOptions);
  return PropertyResource;
}

/**
* Updates a property.
*
* @param {string} fileId The ID of the file.
* @param {string} propertyKey The key of the property.
* @param {object} PropertyResource An object containing the PropertyResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned PropertyResource object
*/
function propertiesUpdate(fileId,propertyKey,PropertyResource,options){
  var path = buildUrl_("files/"+fileId+"/properties/"+propertyKey,options);
  var callOptions = {method:"PUT",payload:JSON.stringify(PropertyResource)};
  var PropertyResource = CALL_(path,callOptions);
  return PropertyResource;
}

/**
* Exports the contents of the Realtime API data model associated with this file as JSON.
*
* @param {string} fileId The ID of the file that the Realtime API data model is associated with.
* @param {object} options Keypair of all optional parameters for this call
*/
function realtimeGet(fileId,options){
  var path = buildUrl_("files/"+fileId+"/realtime",options);
  var callOptions = {method:"GET"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Overwrites the Realtime API data model associated with this file with the provided JSON data model.
*
* @param {string} fileId The ID of the file that the Realtime API data model is associated with.
* @param {object} options Keypair of all optional parameters for this call
*/
function realtimeUpdate(fileId,options){
  var path = buildUrl_("files/"+fileId+"/realtime",options);
  var callOptions = {method:"PUT"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Deletes a reply.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {string} replyId The ID of the reply.
* @param {object} options Keypair of all optional parameters for this call
*/
function repliesDelete(fileId,commentId,replyId,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId+"/replies/"+replyId,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets a reply.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {string} replyId The ID of the reply.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentReplyResource object
*/
function repliesGet(fileId,commentId,replyId,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId+"/replies/"+replyId,options);
  var callOptions = {method:"GET"};
  var CommentReplyResource = CALL_(path,callOptions);
  return CommentReplyResource;
}

/**
* Creates a new reply to the given comment.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {object} CommentReplyResource An object containing the CommentReplyResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentReplyResource object
*/
function repliesInsert(fileId,commentId,CommentReplyResource,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId+"/replies",options);
  var callOptions = {method:"POST",payload:JSON.stringify(CommentReplyResource)};
  var CommentReplyResource = CALL_(path,callOptions);
  return CommentReplyResource;
}

/**
* Lists all of the replies to a comment.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentReplyListResource object
*/
function repliesList(fileId,commentId,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId+"/replies",options);
  var callOptions = {method:"GET"};
  var CommentReplyListItems = CALLPAGE_(path,callOptions,"items");
  return CommentReplyListItems;
}

/**
* Updates an existing reply. This method supports patch semantics.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {string} replyId The ID of the reply.
* @param {object} CommentReplyResource An object containing the CommentReplyResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentReplyResource object
*/
function repliesPatch(fileId,commentId,replyId,CommentReplyResource,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId+"/replies/"+replyId,options);
  var callOptions = {method:"PATCH",payload:JSON.stringify(CommentReplyResource)};
  var CommentReplyResource = CALL_(path,callOptions);
  return CommentReplyResource;
}

/**
* Updates an existing reply.
*
* @param {string} fileId The ID of the file.
* @param {string} commentId The ID of the comment.
* @param {string} replyId The ID of the reply.
* @param {object} CommentReplyResource An object containing the CommentReplyResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned CommentReplyResource object
*/
function repliesUpdate(fileId,commentId,replyId,CommentReplyResource,options){
  var path = buildUrl_("files/"+fileId+"/comments/"+commentId+"/replies/"+replyId,options);
  var callOptions = {method:"PUT",payload:JSON.stringify(CommentReplyResource)};
  var CommentReplyResource = CALL_(path,callOptions);
  return CommentReplyResource;
}

/**
* Removes a revision.
*
* @param {string} fileId The ID of the file.
* @param {string} revisionId The ID of the revision.
* @param {object} options Keypair of all optional parameters for this call
*/
function revisionsDelete(fileId,revisionId,options){
  var path = buildUrl_("files/"+fileId+"/revisions/"+revisionId,options);
  var callOptions = {method:"DELETE"};
  var removeResource = CALL_(path,callOptions);
  return removeResource;
}

/**
* Gets a specific revision.
*
* @param {string} fileId The ID of the file.
* @param {string} revisionId The ID of the revision.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned RevisionResource object
*/
function revisionsGet(fileId,revisionId,options){
  var path = buildUrl_("files/"+fileId+"/revisions/"+revisionId,options);
  var callOptions = {method:"GET"};
  var RevisionResource = CALL_(path,callOptions);
  return RevisionResource;
}

/**
* Lists a file's revisions.
*
* @param {string} fileId The ID of the file.
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned RevisionListResource object
*/
function revisionsList(fileId,options){
  var path = buildUrl_("files/"+fileId+"/revisions",options);
  var callOptions = {method:"GET"};
  var RevisionListResource = CALL_(path,callOptions);
  return RevisionListResource;
}

/**
* Updates a revision. This method supports patch semantics.
*
* @param {string} fileId The ID for the file.
* @param {string} revisionId The ID for the revision.
* @param {object} RevisionResource An object containing the RevisionResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned RevisionResource object
*/
function revisionsPatch(fileId,revisionId,RevisionResource,options){
  var path = buildUrl_("files/"+fileId+"/revisions/"+revisionId,options);
  var callOptions = {method:"PATCH",payload:JSON.stringify(RevisionResource)};
  var RevisionResource = CALL_(path,callOptions);
  return RevisionResource;
}

/**
* Updates a revision.
*
* @param {string} fileId The ID for the file.
* @param {string} revisionId The ID for the revision.
* @param {object} RevisionResource An object containing the RevisionResource for this method
* @param {object} options Keypair of all optional parameters for this call
* @return {object} The returned RevisionResource object
*/
function revisionsUpdate(fileId,revisionId,RevisionResource,options){
  var path = buildUrl_("files/"+fileId+"/revisions/"+revisionId,options);
  var callOptions = {method:"PUT",payload:JSON.stringify(RevisionResource)};
  var RevisionResource = CALL_(path,callOptions);
  return RevisionResource;
}
Categories
Code

Parse Gmail Messages to Extract Data

A mailbox has 1000s of email messages sent though legacy contact forms that contain data like the name, email and address of the senders. The business owner would like to parse these email messages, extract the relevant bits and save them to a Google Spreadsheet.

The script can be run in batches of 100 thread, to avoid exceeding the time limit, and the parsing rules can be written in Regular Expressions. Snippet by @Ferrari.

The code can extended to parse emails and extract other structured data from the message body including events information, order details, travel itineraries, shipping & tracking information, customer records and more.

/* Based on https://gist.github.com/Ferrari/9678772 */

function parseEmailMessages(start) {

  start = start || 0;

  var threads = GmailApp.getInboxThreads(start, 100);
  var sheet = SpreadsheetApp.getActiveSheet();

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

    // Get the first email message of a threads
    var tmp,
      message = threads[i].getMessages()[0],
      subject = message.getSubject(),
      content = message.getPlainBody();

    // Get the plain text body of the email message
    // You may also use getRawContent() for parsing HTML

    // Implement Parsing rules using regular expressions
    if (content) {

      tmp = content.match(/Name:\s*([A-Za-z0-9\s]+)(\r?\n)/);
      var username = (tmp && tmp[1]) ? tmp[1].trim() : 'No username';

      tmp = content.match(/Email:\s*([A-Za-z0-9@.]+)/);
      var email = (tmp && tmp[1]) ? tmp[1].trim() : 'No email';

      tmp = content.match(/Comments:\s*([\s\S]+)/);
      var comment = (tmp && tmp[1]) ? tmp[1] : 'No comment';

      sheet.appendRow([username, email, subject, comment]);

    } // End if

  } // End for loop
}

Categories
Code

Send Email Messages from Gmail to Slack Channel

The Google Apps Script, courtesy @andrewwilson, uses incoming webhooks to post email messages from your Gmail mailbox into Slack channel. The email body is extracted using the GmailApp service and posted to Slack through an HTTP request with a JSON payload. You need to create a ToSlack gmail label and apply this label to message that you wish to post to your Slack channel.

A time-based trigger may also be added for sending labeled message from Gmail to Slack in near real-time.


/* Credit: gist.github.com/andrewmwilson */

function sendEmailsToSlack() {
    var label = GmailApp.getUserLabelByName('ToSlack');
    var messages = [];
    var threads = label.getThreads();
  
    for (var i = 0; i < threads.length; i++) {
        messages = messages.concat(threads[i].getMessages())
    }

    for (var i = 0; i < messages.length; i++) {
        var message = messages[i];
        Logger.log(message);

        var output = '*New Email*';
        output += '\n*from:* ' + message.getFrom();
        output += '\n*to:* ' + message.getTo();
        output += '\n*cc:* ' + message.getCc();
        output += '\n*date:* ' + message.getDate();
        output += '\n*subject:* ' + message.getSubject();
        output += '\n*body:* ' + message.getPlainBody();
        Logger.log(output);

        var payload = {
            'username': 'gmail-bot',
            'text': output,
            'channel' : '#some-channel',
            'icon_emoji': ':hear_no_evil:',
        };

        var options = {
            'method' : 'post',
            'payload' : Utilities.jsonStringify(payload),
        };

        // replace this with your own Slack webhook URL
        // https://crowdscores.slack.com/services
        var webhookUrl = 'https://hooks.slack.com/services/****/****/****';
        UrlFetchApp.fetch(webhookUrl, options);
   }

   // remove the label from these threads so we don't send them to
   // slack again next time the script is run
   label.removeFromThreads(threads);
}
Categories
Code

Twitter Bot for Auto-Favoriting and Retweeting Tweets

The Twitter bot, written with Google Apps Script, will auto-favorite and retweet tweets every few minutes. Do include the Twitter API library in your Google Scripts project as discussed in the previous tutorial on creating Twitter bots.


// Written by Amit Agarwal @labnol on 31/07/2015

// Fill the Twitter Keys and then choose Run -> Start Bot

TWITTER_CONSUMER_KEY    = "123";
TWITTER_CONSUMER_SECRET = "123";
TWITTER_ACCESS_TOKEN    = "123";
TWITTER_ACCESS_SECRET   = "123";
TWITTER_SEARCH_PHRASE   = "filter:links labnol.org";

function Start_Bot() {
  
  var props = PropertiesService.getScriptProperties();
  
  props.setProperties({
    TWITTER_CONSUMER_KEY: TWITTER_CONSUMER_KEY,
    TWITTER_CONSUMER_SECRET: TWITTER_CONSUMER_SECRET,
    TWITTER_ACCESS_TOKEN: TWITTER_ACCESS_TOKEN,
    TWITTER_ACCESS_SECRET: TWITTER_ACCESS_SECRET,
    SINCE_TWITTER_ID: 0
  });
  
  var twit = new Twitter.OAuth(props);
  
  // Test Twitter authorization
  
  if (!twit.favorite("628053456071192576")) {
    throw new Error("Please check your Twitter access tokens");
    return;
  }
  
  ScriptApp.newTrigger("labnol_twitterBot")
  .timeBased()
  .everyMinutes(10)
  .create();
  
}

function labnol_twitterBot() {
  
  try {
    
    var props = PropertiesService.getScriptProperties(),
        twit = new Twitter.OAuth(props);
    
    if (twit.hasAccess()) {
      
      var tweets = twit.fetchTweets(
        TWITTER_SEARCH_PHRASE, function(tweet) {
          // Skip tweets that contain sensitive content
          if (!tweet.possibly_sensitive) {
            return tweet.id_str;
          }
        }, {
          multi: true,
          lang: "en", // Process only English tweets
          count: 5,   // Process 5 tweets in a batch
          since_id: props.getProperty("SINCE_TWITTER_ID")
        });
      
      if (tweets) {
        
        props.setProperty("SINCE_TWITTER_ID", tweets[0]);
        
        for (var i = tweets.length - 1; i >= 0; i--) {
          
          twit.retweet(tweets[i]);
          twit.favorite(tweets[i]);
          
          /* Wait between 10 seconds and 1 minute */
          Utilities.sleep(Math.floor(Math.random()*50000) + 10000);
          
        }
      }
    }
    
  } catch (f) {
    Logger.log("Error: " + f.toString());
  }
  
}

// Email: amit@labnol.org
// Premium Support: http://ctrlq.org

Categories
Code

Get the Full Path of a File in Google Drive

In Google Drive, you can have a file inside a folder which can be located inside another folder and so on. Thus Google Script will print (writes to the console log) the full folder path of any file in your Google Drive starting from the root folder which is called “My Drive” in Google Drive.

If the Drive folder path is blank or missing, it is likely because the file is shared with you but not added to your Drive yet.


// Provide the file name
function getFullFolderPath(fileName) {

  // Search for the file with that name and process the first result
  var files = DriveApp.getFilesByName(fileName);

  if (files.hasNext()) {

    var file = files.next(),
      folders = [],
      parent = file.getParents();

    if (parent) {
      // Display the URL of the containing folder
      Logger.log("Folder URL: " + parent.getUrl());
    }

    while (parent.hasNext()) {

      parent = parent.next();

      folders.push(parent.getName());

      parent = parent.getParents();

    }

    if (folders.length) {
      // Display the full folder path
      Logger.log("Folder path: " + folders.reverse().join("/"));
    }

  }

}

Categories
Code

Upload Files to Google Drive with Google Apps Script

Google Forms do not offer the file uploads feature but you can use Google Apps Script and let anyone upload files to Google Drive through an HTML web form.

For instance, you can share the form with your class, or with your clients, and they can directly upload school assignments, photographs, and other documents to a specific folder in your Google Drive.

There are two steps involved here. First you need to create an HTML form using HTML and CSS.

Here’s a sample form that uses the Materialize CSS library to give the standard Google Forms like material look to your file upload form.










 




The server side Google Script code includes a function for processing the form input. It reads the uploaded file as a blob and saves the blob as a new file into your Google Drive. The file name, extension, and content type are preserved.

[*] The premium version of the file upload form (demo form) lets you visually create forms and allow file uploads of any size via the Google File Picker API.

/* The script is deployed as a web app and renders the form */
function doGet(e) {
  return HtmlService
    .createHtmlOutputFromFile('forms.html')
    .setTitle("Google File Upload by CTRLQ.org");
}

function uploadFileToGoogleDrive(data, file, name, email) {

  try {

    var dropbox = "My Dropbox";
    var folder, folders = DriveApp.getFoldersByName(dropbox);

    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      folder = DriveApp.createFolder(dropbox);
    }

    var contentType = data.substring(5,data.indexOf(';')),
        bytes = Utilities.base64Decode(data.substr(data.indexOf('base64,')+7)),
        blob = Utilities.newBlob(bytes, contentType, file);

    folder.createFolder([name, email].join(" ")).createFile(blob);

    return "OK";

  } catch (f) {
    return f.toString();
  }

}

You can save the two files and deploy the Google script as a web app with access to anyone, including anonymous as shown in this GIF.

Categories
Code

Using Blogger API with Google Apps Script

Like WordPress, you can also manage your Blogger blogs using Google Apps Scripts. You need to enable the Blogger API from your Google Developers console and also include the Apps Script oAuth2 library in your Google Apps script project. The scope should be set to either of the following depending on whether to want read or write access to blogs.

1. https://www.googleapis.com/auth/blogger
2. https://www.googleapis.com/auth/blogger.readonly

The snippet connects to the Blogger API and fetches the list of Blogger blogs of the currently authenticated users. It then outputs the blog’s ID, name and blog URL in the console log.

function bloggerAPI() {
  
  var api = "https://www.googleapis.com/blogger/v3/users/self/blogs";
  
  var headers = {
    "Authorization": "Bearer " + getService().getAccessToken()
  };
  
  var options = {
    "headers": headers,
    "method" : "GET",
    "muteHttpExceptions": true
  };
  
  var response = UrlFetchApp.fetch(api, options);
  
  var json = JSON.parse(response.getContentText());
  
  for (var i in json.items) {
    Logger.log("[%s] %s %s", json.items[i].id, json.items[i].name, json.items[i].url); 
  } 
}

In the next example, we using the Blogger API to update the title and content of a blog post through Script. We update the post through Patch Semantics which allows us to send only fields that have changed or need to updated. Since UrlFetchApp doesn’t allow HTTP PATCH requests, we do an HTTP POST request and set the override X-HTTP-Method-Override header to PATCH, as shown below:

function updatePost(blogID, postID) {
  
  var url = "https://www.googleapis.com/blogger/v3/blogs/" + blogID + "/posts/" + postID;
  
  var payload = {
    "title"   : "This is the post title",
    "content" : "This is HTML post"
  };
  
  var headers = {
    "Authorization": "Bearer " + getService().getAccessToken(),
    "X-HTTP-Method-Override": "PATCH"
  };
  
  var options = {
    "headers": headers,
    "method" : "POST",
    "muteHttpExceptions" : true,
    "payload": JSON.stringify(payload),
    "contentType": "application/json"
  }
  
  var response = UrlFetchApp.fetch(url, options);
  
  Logger.log(response.getContentText());

}

Troubleshooting: If you fetching the post status (draft, live or scheduled), you need to set the view parameter as “ADMIN” in the API call. For 403 forbidden errors that say “We’re sorry, but you don’t have permission to access this resource” – it is likely that you have only read-only or view access to a blog.

Categories
Code

Search Twitter with Google Apps Script

The Twitter Search Archiver uses the OAuth1 library for Google Apps Script to talk to the Twitter Search API. You need to include the OAuth1 library in your Google Script project, create a Callback URL and then call the authorization URL in a separate browser tab to authorize the Twitter service.

However, if you are only going to make read-only requests to Twitter – like fetching user timelines for Twitter RSS feeds or performing searches – you can do without the library as well. You won’t be able to post tweets or DMs though.

Twitter supports application-only authentication giving apps the ability to issue authenticated requests on behalf of the application itself without authenticating the specific user.

First create a new Twitter app and make a note of the Twitter consumer key and consumer secret. The Callback URL can be left blank since it is not required.


/* Based on thinkAmi-sandbox/GAS-TwitterOAuth2-sample */

function Twitter() {
  
  // Encode consumer key and secret
  var tokenUrl = "https://api.twitter.com/oauth2/token";
  var tokenCredential = Utilities.base64EncodeWebSafe(
    TWITTER_CONSUMER_KEY + ":" + TWITTER_CONSUMER_SECRET);
  
  //  Obtain a bearer token with HTTP POST request
  var tokenOptions = {
    headers : {
      Authorization: "Basic " + tokenCredential,
      "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8" 
    },
    method: "post",
    payload: "grant_type=client_credentials"
  };
  
  var responseToken = UrlFetchApp.fetch(tokenUrl, tokenOptions);
  var parsedToken = JSON.parse(responseToken);
  var token = parsedToken.access_token;
  
  // Authenticate Twitter API requests with the bearer token
  var apiUrl = "https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=labnol";
  var apiOptions = {
    headers : {
      Authorization: 'Bearer ' + token
    },
    "method" : "get"
  };
  
  var responseApi = UrlFetchApp.fetch(apiUrl, apiOptions);
  
  var result = "";
  
  if (responseApi.getResponseCode() == 200) {
    
    // Parse the JSON encoded Twitter API response
    var tweets = JSON.parse(responseApi.getContentText());
    
    if (tweets) {
      
      for (var i = 0; i < tweets.length; i++) {
        var tweet = tweets[i].text;
        var date = new Date(tweets[i].created_at);
        result += "[" + date.toUTCString() + "]" + tweet + " / ";
      }
    }
  }
  
  Logger.log(result);
  
}
Categories
Code

Automatically Print Files Placed in Drive with Google Cloud Print and Apps Script

With Google Cloud Print, you can manage your printers and create print jobs from any computer or mobile device even if they are not connected to the printer or they are on a different network. Cloud Printer also includes a special “Save to Google Docs” virtual printer that lets you save web pages and documents as PDFs in your Google Drive.

In this tutorial, you’ll learn how to use the Google Cloud Print API from Google Apps Scripts. You can use such a system to enable auto-printing, i.e., print a file as soon as it is uploaded in your Google Drive or print an email thread from Gmail. Remote printing is also possible via Dropbox but Cloud Print runs on Google Servers and all you need is a Mac or Windows computer running the Chrome browser (the connector).

To get started, create a new Google Apps Script project and include the OAuth2 library. Next go to the developer console of that project and create a new web application. Set the Redirect URL as below and set the allowed domains as script.google.com (the Project Key is under File – Project Properties).

https://script.google.com/macros/d/{PROJECT KEY}/usercallback

Add this code to the Apps Script project, replace the Client ID and Client Secret with the actual values from Developer Console and save the project. Go to Run – ShowURL and authorize the script. Open the Logger (Cmd + Enter), copy the URL and paste it in a new browser tab to complete the authorization.


function showURL() {
  var cpService = getCloudPrintService();
  if (!cpService.hasAccess()) {
    Logger.log(cpService.getAuthorizationUrl());
  }
}

function getCloudPrintService() {
  return OAuth2.createService('print')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setClientId('CLIENT_ID')
    .setClientSecret('CLIENT_SECRET')
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('https://www.googleapis.com/auth/cloudprint')
    .setParam('login_hint', Session.getActiveUser().getEmail())
    .setParam('access_type', 'offline')
    .setParam('approval_prompt', 'force');
}

function authCallback(request) {
  var isAuthorized = getCloudPrintService().handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('You can now use Google Cloud Print from Apps Script.');
  } else {
    return HtmlService.createHtmlOutput('Cloud Print Error: Access Denied');
  }
}

Now that your Cloud Print API script is authorized, we can send print jobs or run queries. For instance, if you are to retrieve a list of printers connected to Google Cloud Print, the code would be something like this:

function getPrinterList() {

  var response = UrlFetchApp.fetch('https://www.google.com/cloudprint/search', {
    headers: {
      Authorization: 'Bearer ' + getCloudPrintService().getAccessToken()
    },
    muteHttpExceptions: true
  }).getContentText();

  var printers = JSON.parse(response).printers;

  for (var p in printers) {
    Logger.log("%s %s %s", printers[p].id, printers[p].name, printers[p].description);
  }

Similarly, to print a file in your Google Drive, you can write a routine like this. The docID is the ID of your Google Document that resides in Drive.

function printGoogleDocument(docID, printerID, docName) {

  var ticket = {
    version: "1.0",
    print: {
      color: {
        type: "STANDARD_COLOR",
        vendor_id: "Color"
      },
      duplex: {
        type: "NO_DUPLEX"
      }
    }
  };

  var payload = {
    "printerid" : printerID,
    "title"     : docName,
    "content"   : DriveApp.getFileById(docID).getBlob(),
    "contentType": "application/pdf",
    "ticket"    : JSON.stringify(ticket)
  };

  var response = UrlFetchApp.fetch('https://www.google.com/cloudprint/submit', {
    method: "POST",
    payload: payload,
    headers: {
      Authorization: 'Bearer ' + getCloudPrintService().getAccessToken()
    },
    "muteHttpExceptions": true
  });

  response = JSON.parse(response);

  if (response.success) {
    Logger.log("%s", response.message);
  } else {
    Logger.log("Error Code: %s %s", response.errorCode, response.message);
  }
}

These can be extended to print web pages, images or any file that is preset in your Google Drive. You’ll have to modify the “content-Type” based on the mimeType of the file. The valid values are:

"url" (for web pages)
"dataUrl" (for printing base64 encoded images)
"google.drive" (Google Drive files)
"google.kix" (Google Document)
"google.mail" (Gmail thread)
"google.presentation" (Google Presentations)
"google.spreadsheet" (Google Spreadsheets)

If you looking for a automated system to email Google Spreadsheets as PDF, Google Cloud Print maybe too complex a solution, try this alternative.

Categories
Code

Know Who Changed a File in Google Drive with Apps Script

Google Drive maintains a history of all edits made to a file and this information comes if you are trying to figure out who modified the files in your Google Drive. You can know the date and time when a file was modified, the name of the user who made the edit and their email address is available as well.

To get started, you need to enable the Advanced Google Drive API in the Google Services section of your Google Apps Script project. You can use the method to track revisions made to your Google Sheets, Presentations, Documents and other native Google Formats. The script can be further enhanced to send you email alerts as soon as a file is updated in Drive.

Also see: Get your Google Drive Activity Report

/* Credit: +Jean-Pierre Verhulst */

function listFileRevisions(fileID) {
    
  var editList = [],
      revisions = Drive.Revisions.list(fileID);
  
  if (revisions.items && revisions.items.length > 0) {
    for (var i=0; i < revisions.items.length; i++) {
      var revision = revisions.items[i];
      editList.push([revision.id, (new Date(revision.modifiedDate)).toLocaleString(),
        revision.lastModifyingUserName, revision.lastModifyingUser.emailAddress
      ]);
    }
    Logger.log(editList);
  } else {
    Logger.log('No file revisions found.');
  }

}
Categories
Code

Get EXIF Data and GPS Location of Images in Google Drive

When you upload your images to Google Photos, they also become available in Google Drive and you can these access these image files with Google Apps Scripts and the Google Drive API.

Here’s a little snippet that will show you how to get the GPS location data, capture date and EXIF information of any photograph in your Google Drive. You’ll need to enable the Google Drive service to access the file’s meta data. The same technique can used to get the size and duration of a video file that’s in your Drive.

function getFileMetaData(fileID) {
    
  var api = "https://www.googleapis.com/drive/v2/files/" + fileID;
  
  var params = {
    method:"get",
    contentType: "application/json",
    headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions:true,
  };
  
  var meta = JSON.parse(UrlFetchApp.fetch(api, params).getContentText());
  
  Logger.log(meta.title); // File name
  Logger.log(meta.imageMediaMetadata); // Photo EXIF data like Camera, Date taken, Focal Length, Lens, etc.
  Logger.log(meta.imageMediaMetadata.location); // Photo Location coordinates
  Logger.log(meta.videoMediaMetadata); // Video Size and Length.
}
Categories
Code

Export Formulas and Notes from a Google Spreadsheet

Dan Thareja has written a Google Script that will let you export all the formulas, cell values and notes from a Google Spreadsheet as a JSON file. You can also pull the Google formulas in another web app or Google Apps script using a GET Request. It should come handy when you are reviewing the formulas of a large sheet.


// Credit: github.com/danthareja

function doGet(request) {
  
  // ID of Google Spreadsheet
  var json = getNotesAndFormulas(request.parameter.id);
  
  return ContentService.createTextOutput(JSON.stringify(cache))
  .setMimeType(ContentService.MimeType.JSON);
}

function getNotesAndFormulas(spreadsheetId) {
  return SpreadsheetApp
  .openById(spreadsheetId)
  .getSheets()
  .reduce(function(cache, sheet) {
    
    var sheetData = cache[sheet.getName()] = {};
    var range = sheet.getDataRange();
    
    sheetData.range = range.getA1Notation();
    sheetData.notes = range.getNotes();
    sheetData.formulas = range.getFormulas();
    
    return cache;
  }, { spreadsheetId: spreadsheetId });
}
Categories
Code

How to Make a Twitter Bot with Google Scripts

You can easily write a Twitter bot with the help of Google Apps Script. It fetches tweets addressed (@mentions) to the bot through the Twitter API, computes the answer with Wolfraph Alpha and replies with a tweet. The steps are:

  • Create a Twitter App at apps.twitter.com (screenshot) and note the Twitter Consumers Keys and Access Tokens
  • Go to developer.wolfram.com, create an account screenshot and then choose “Get an App ID” to create your new app. We will need this App ID in the Google Script. Your free Wolfram Alpha App can only be used for a non-commercial purpose.
  • Edit the values of TWITTER_CONSUMER_KEY, TWITTER_CONSUMER_SECRET, TWITTER_ACCESS_TOKEN, TWITTER_ACCESS_SECRET, TWITTER_HANDLE (your Twitter account) and WOLFRAM_API_ID
  • Go to Run -> Start to initialize the Twitter bot. Say yes if the script requires you to authorize access to certain Google services.

The code is ready to though you’ll need to create a Wolfram App and also include the Twitter library in your Google Scripts project (key is MKvHYYdYA4G5JJHj7hxIcoh8V4oX7X1M_) with the identifier set to Twitter.


/* Twitter Bot written by Amit Agarwal */

function Start() {

  var props = PropertiesService.getScriptProperties();

  props.setProperties({
    TWITTER_CONSUMER_KEY: TWITTER_CONSUMER_KEY,
    TWITTER_CONSUMER_SECRET: TWITTER_CONSUMER_SECRET,
    TWITTER_ACCESS_TOKEN: TWITTER_ACCESS_TOKEN,
    TWITTER_ACCESS_SECRET: TWITTER_ACCESS_SECRET,
    MAX_TWITTER_ID: 0
  });

  // Delete exiting triggers, if any

  var triggers = ScriptApp.getProjectTriggers();

  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  // Setup a time-based trigger for the Bot to fetch and process incoming Tweets 
  // every minute. If your Google Script is running out of quota, change the
  // time to 5 or 10 minutes though the bot won't offer real-time answers then.

  ScriptApp.newTrigger("labnol_twitterBot")
    .timeBased()
    .everyMinutes(1)
    .create();

}

/* For help, email amit@labnol.org or visit http://ctrlq.org */

function labnol_twitterBot() {

  try {

    var props = PropertiesService.getScriptProperties(),
      twit = new Twitter.OAuth(props);

    // Are the Twitter access tokens are valid?
    if (twit.hasAccess()) {

      var tweets = twit.fetchTweets("to:" + TWITTER_HANDLE,
        function(tweet) {
          // Ignore tweets that are sensitive (NSFW content)
          if (!tweet.possibly_sensitive) {
            var question = tweet.text.toLowerCase().replace("@" + TWITTER_HANDLE, "").trim(),
              answer = askWolframAlpha_(question);
            if (answer) {
              return {
                answer: "@" + tweet.user.screen_name + " " + answer,
                id_str: tweet.id_str
              };
            }
          }
        }, {
          multi: true,
          lang: "en",  // Fetch only English tweets
          count: 5,    // Process 5 tweets in a batch
          since_id: props.getProperty("MAX_TWITTER_ID")
        });

      if (tweets.length) {

       // The MAX_TWITTER_ID property store the ID of the last tweet answered by the bot
        props.setProperty("MAX_TWITTER_ID", tweets[0].id_str);

        // Process the tweets in FIFO order
        for (var i = tweets.length - 1; i >= 0; i--) {

          // The bot replies with an answer
          twit.sendTweet(tweets[i].answer, {
            in_reply_to_status_id: tweets[i].id_str
          });

          // Wait a second to avoid hitting the rate limits
          Utilities.sleep(1000);
        }
      }
    }

  } catch (f) {
    // You can also use MailApp to get email notifications of errors.
    Logger.log("Error: " + f.toString());
  }

}

function askWolframAlpha_(q, app) {

  try {

    var api = "http://api.wolframalpha.com/v2/query?podindex=2&format=plaintext&appid=" 
                   + WOLFRAM_APP_ID + "&input=" + encodeURIComponent(q);
    var response = UrlFetchApp.fetch(api, {
      muteHttpException: true
    });

    // Parse the XML response
    if (response.getResponseCode() == 200) {
      var document = XmlService.parse(response.getContentText());
      var root = document.getRootElement();
      if (root.getAttribute("success").getValue() === "true") {
        return root.getChild("pod").getChild("subpod").getChild("plaintext").getText();
      }
    }
  } catch (f) {}
  return false;
}

Categories
Code

Save Gmail Messages to a Google Spreadsheet

The Google Script from @oshliaer will save the body of email messages from Gmail to the currently active worksheet inside Google Spreadsheet. You need to specify the Gmail search query and the sheet ID where the matching messages are to be exported. It saves the text content of the message sans any HTML tags or images.

To get started, paste the code in the script editor of a Google Spreadsheet and run SaveEmail from the Run menu.

Also see: Save Gmail Attachment to Google Drive


var SEARCH_QUERY = "label:inbox is:unread to:me";
 
// Credit: https://gist.github.com/oshliaer/70e04a67f1f5fd96a708

function getEmails_(q) {
    var emails = [];
    var threads = GmailApp.search(q);
    for (var i in threads) {
        var msgs = threads[i].getMessages();
        for (var j in msgs) {
            emails.push([msgs[j].getBody().replace(/<.*?>/g, '\n')
                .replace(/^\s*\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n')
            ]);
        }
    }
    return emails;
}

function appendData_(sheet, array2d) {
    sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);
}

function saveEmails() {
    var array2d = getEmails_(SEARCH_QUERY);
    if (array2d) {
        appendData_(SpreadsheetApp.getActiveSheet(), array2d);
    }
}

Categories
Code

Connect to Insightly API using Google Scripts

You can use Google Apps Script to talk to the Insightly API and the project data can be integrated with other Google Services like Google Spreadsheet or Gmail. In a recent project, the Project details stored in Insightly CRM were saved as individual documents in Google Drive which were then shared with the project owner automatically.

function connectToInsightly(endp) {

  var key = "<>";
  var api = "https://api.insight.ly" + endp;

  var response = UrlFetchApp.fetch(api, {
    muteHTTPexceptions: true,
    headers: {
      'Authorization': 'Basic ' + Utilities.base64Encode(key),
      'Content-Type': 'application/json'
    }
  });

  return JSON.parse(response.getContentText());

}

function getProjectDetails() {

  var endp = "/v2.1/Projects";
  var projects = connectToInsightly(endp);

  for (var p in projects) {
    Logger.log(projects[p]);
  }
}

function getCustomFields() {

  var endp = "/v2.1/CustomFields";
  var response = connectToInsightly(endp);
  var cf = {};

  for (var i in response) {
    cf[response[i].CUSTOM_FIELD_ID] = response[i].FIELD_NAME;
  }

  Logger.log(cf);

}
Categories
Code

Send Gravity Forms Data to Google Spreadsheet or Email

Gravity Forms is an extremely popular Forms plugin for the WordPress. When someone submits a form created with Gravity Forms, the form data is saved inside the MySQL database associated with your WordPress installation. There are paid add-ons through, Zapier for example, that let you do more when someone submits a Form.

For instance, you can setup a task in Zapier that will automatically save the Gravity Form data to a specific Google Spreadsheet. Or you can setup a rule where the data is emailed to you as soon as a form is submitted.

Zapier offers a visual tool to maps your Gravity Forms to Google Spreadsheets but you can do something similar with Google Apps Script and WordPress hooks without needing to subscribe to Zapier. Let me show you how:

From Gravity Forms to Google Spreadsheets

First we need to create a web-app with Google Scripts that will receive the Form data from Gravity Forms and either save it to Google Sheets or send it via Gmail. Also see: Get Google Forms Data in Email.

Open the Google Spreadsheet where you wish to save the Forms data and create a header row with the column names for all the fields that you wish to save from Gravity Forms. Next go to Tools, Script Editor and paste the following snippet.

function doPost(e) {

  if (!e) return;

  var sheetID = "GOOGLE_SPREADSHEET_ID";  // Replace this with the Google Spreadsheet ID
  var sheetName = "Form Responses";       // Replace this with the sheet name inside the Spreadsheet

  var status = {};

  // Code based on Martin Hawksey (@mhawksey)'s snippet

  var lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {

    var sheet = SpreadsheetApp.openById(sheetID).getSheetByName(sheetName);
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

    // Add the data and time when the Gravity Form was submitted
    var column, row = [],
      input = {
        "timestamp": new Date()
      };

    for (var keys in e.parameter) {
      input[normalize_(keys)] = e.parameter[keys];
    }

    for (i in headers) {
      column = normalize_(headers[i])
      row.push(input[column] || "");
    }

    if (row.length) {

      sheet.appendRow(row);
      status = {
        result: "success",
        message: "Row added at position " + sheet.getLastRow()
      };

    } else {
      status = {
        result: "error",
        message: "No data was entered"
      };
    }

  } catch (e) {

    status = {
      result: "error",
      message: e.toString()
    };

  } finally {

    lock.releaseLock();

  }

  return ContentService
    .createTextOutput(JSON.stringify(status))
    .setMimeType(ContentService.MimeType.JSON);

}

function normalize_(str) {
  return str.replace(/[^\w]/g, "").toLowerCase();
}

Save the Google Script. Go to the Run menu and choose doPost to authorize the Google Scripts. Next choose Publish, Deploy as web app and save your work. Click Save New Version, set access as Anyone, even anonymous and click Deploy. Make a note of the Google Script URL as we will need it in the WordPress snippet.

From WordPress to Google Spreadsheets

Now we need to write an Action Hook on WordPress side that will send the data to Google Script which will then save the data to Google Spreadsheet. Go your WordPress theme folder and paste this snippet inside your functions.php file.

 rgar($entry, '1'), 'age' => rgar($entry, '2'), 'sex' => rgar($entry, '3'),);

    // Send the data to Google Spreadsheet via HTTP POST request
    $request = new WP_Http();
    $response = $request->request($post_url, array('method' => 'POST', 'sslverify' => false, 'body' => $body));
}
?>

Save the PHP file and submit a test entry. It should show up in your Google Spreadsheet instantly.

Categories
Code

Find Location & IP Address of Email Sender

If you would like to know the IP Address and Location of email sender in Gmail, Google Scripts can help. It extracts the raw headers of the email message and then pull the IP address of the sender using regular expressions.

The IP address is then passed to a free GeoIP service that gives the geographic location of an IP address. The JSON response may contain the country name, city and even the zip code in some cases.

Also see: Find Person by Email Address


function getEmailCountry(messageID) {
  
  var message = GmailApp.getMessageById(messageID);
  var raw = message.getRawContent().split("\n");
  
  for (var j=0; j < raw.length; j++) {
    
    var ipaddress = /Received:\s+from.*?\[((?:[0-9]{1,3}\.){3}[0-9]{1,3})\]/i.exec(raw[j]); 
    
    if (ipaddress != null) {
      
      var json, reply = {country_name: "Unknown"};
      
      try {          
        json = UrlFetchApp.fetch("http://freegeoip.net/json/" + ipaddress[1]);
        reply = JSON.parse(json);
      } catch (e) {}
      
      Logger.log("The email is sent from " + ipaddress[1] + " in " + reply.country_name);        
      break;
      
    }
    
  }
}
Categories
Code

Upload Files to HTML Form with Google Scripts

The Google Script will automatically download a file from the Internet and upload it to another HTML form with an HTTP POST request. If the file already exists in your Google Drive, you can use the DriveApp.getFileById() method to retrieve the file and send it to the web form. Also see: Receive Files in Google Drive


// From: https://developers.google.com/adwords/scripts

function downloadFile() {

  // Download a file now (GET), so we can upload it in the HTTP POST below.
  var response = UrlFetchApp.fetch('https://www.google.com/humans.txt');
  var fileBlob = response.getBlob();

  var payload = {
      'fieldOne' : 'value for field one',
      'fieldTwo' : 'value for field two',
      'fileAttachment': fileBlob
  };

  // Because payload is a JavaScript object, it will be interpreted as
  // an HTML form. (We do not need to specify contentType; it will
  // automatically default to either 'application/x-www-form-urlencoded'
  // or 'multipart/form-data')

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

  UrlFetchApp.fetch('http://example.com/upload_form.cgi', options);
}
Categories
Code

Archive Old Email Messages in Gmail Automatically

The Google Script will archive all email threads in a specific Gmail label that have been inactive for more than a week. It takes the date of the last message in a thread and if it is older than a week, the thread is archived. The current labels is removed as well.

function ArchiveOldEmails(gmailLabelName){
  
  var gmailLabel = GmailApp.getUserLabelByName(gmailLabelName);
  
  var ONE_WEEK = 60 * 60 * 24 * 7 * 1000;
  
  var threads = label.getThreads();
  var now     = new Date();
  
  for (var j=0; j ONE_WEEK){
      threads[j].moveToArchive().removeLabel(gmailLabel);
    }
  }
  
}
Categories
Code

Convert Image to PDF to Google Script

The Google Script will fetch an image file, convert it to PDF and sends it as an image attachment using the Gmail service. The image file can be on your Google Drive or it can be on web (specify the image URL as the filename).

Also see: How to Convert Files

function convertImageToPDF(filename) {
  
  var image;
  
  // Is it a local file or web URL?
  if (filename.match(/^https?:\/\//i)) {
    image = UrlFetchApp.fetch(filename);
  } else {
    image = DriveApp.getFilesByName(filename).next();
  } 
  
  // grab its bytes and base64-encode them.
  var base64 = Utilities.base64Encode(image.getBlob().getBytes());
  var html = '';
  
  // create a blob, convert to PDF
  var blob = Utilities.newBlob(html, MimeType.HTML).setName(filename + ".pdf");
  
  MailApp.sendEmail("ctrlq@labnol.org", "Image to PDF", "", {
    attachments:blob.getAs(MimeType.PDF)
  });
  
}
Categories
Code

Save Gmail Messages as Google Documents

The Google Script will save any Gmail message (or thread) in your Google Drive as a native Google Document with proper formatting. Unlike the Save Gmail as PDF script that downloads the email threads as PDF files in your Google Drive, this Google Script create a Google Docs file for your Gmail message and these do not count against the storage quota.


function saveGmail(msgID) {
    
  // Based on Drive Scoop
  // Available at https://github.com/google/gfw-deployments

  var message = GmailApp.getMessageById(msgID);
  
  // Grab the message's headers.
  var from = message.getFrom();
  var subject = message.getSubject();
  var to = message.getTo();
  var cc = message.getCc();
  var date = message.getDate();
  var body = message.getBody();
  
  // Begin creating a doc.
  var document = DocumentApp.create(subject);  
  var document_title = document.appendParagraph(subject);  
  document_title.setHeading(DocumentApp.ParagraphHeading.HEADING1);
  
  var style = {};
  style[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = (DocumentApp.HorizontalAlignment.CENTER);
  document_title.setAttributes(style);
  
  var headers_heading = (document.appendParagraph("Gmail Message Headers"));
  headers_heading.setHeading(DocumentApp.ParagraphHeading.HEADING2);
  
  AddGmailHeaderToDoc(document, "From", from);
  AddGmailHeaderToDoc(document, "To", to);
  AddGmailHeaderToDoc(document, "Cc", cc);
  AddGmailHeaderToDoc(document, "Date", date);
  AddGmailHeaderToDoc(document, "Subject", subject);
  
  var body_heading = (
    document.appendParagraph("Body (without Markup)"));
  body_heading.setHeading(DocumentApp.ParagraphHeading.HEADING2);
  
  var sanitized_body = body.replace(/<\/div>/, "\r\r");
  sanitized_body = sanitized_body.replace(//g, "\r");
  sanitized_body = sanitized_body.replace(/<\/p>/g, "\r\r");
  sanitized_body = sanitized_body.replace(/<.*?>/g, "");
  sanitized_body = sanitized_body.replace(/'/g, "'");
  sanitized_body = sanitized_body.replace(/"/g, '"');
  sanitized_body = sanitized_body.replace(/&/g, "&");
  sanitized_body = sanitized_body.replace(/\r\r\r/g, "\r\r");
  
  var paragraph = document.appendParagraph(sanitized_body);
  
  document.saveAndClose();
  
  return document.getUrl();
  
}

function AddGmailHeaderToDoc(document, header_name, header_value) {
  if (header_value === "") return;
  var paragraph = document.appendParagraph("");
  paragraph.setIndentStart(72.0);
  paragraph.setIndentFirstLine(36.0);
  paragraph.setSpacingBefore(0.0);
  paragraph.setSpacingAfter(0.0);    
  var name = paragraph.appendText(header_name + ": ");
  name.setBold(false);
  var value = paragraph.appendText(header_value);
  value.setBold(true);
}
Categories
Code

Replace Adobe Forms Central with Google Forms

Adobe is retiring their Forms Central product in the next few weeks. You’ll be download the Form Responses to Excel, CSV, or PDF formats.

If you are looking for an alternative to Adobe FormsCentral, consider Google Forms. It allows unlimited form responses, they are saved in a Google Spreadsheet and the forms are hosted on Google servers. You can even include complex validations in Google Forms using regular expressions.

One of the most popular feature of Adobe Forms is that it can email responses to one or more users when a form is submitted. This feature is not available in native Google Forms but you can easily add it with a simple Google Scripts.

Read tutorial on how to get Google Forms data in Email.

The script can be configured to send a copy of the form responses to the form submitter, you can send form data as HTML table or a PDF and there are other possibilities too. Like you can have a native HTML form that allows file uploads and saves the files to Google Drive.

Unlike Adobe Forms, there’s no option to integrate PayPal with Google Forms but again, there are Google Scripts that can work on the email notifications to handle PayPal transactions.

Categories
Code

Using Google Picker with Google Apps Script

The Google Picker API lets you select files and folders in Google Drive visually. It can used inside Google Spreadsheet, Google Docs or a standalone HTML web app.

Here’s a snippet that will let user select one or more Drive folders with the Google File Picker API bound inside a Google Spreadsheet. The dialog window only shows the Folders list and all the other files in the Drive stay hidden. When the user selects a folder, the ID of the Drive folder is printed else the dialog is closed.


// code.gs

// See: https://developers.google.com/picker/


function onOpen() {
  SpreadsheetApp.getUi().createMenu('Google Picker')
      .addItem('Choose Folder', 'showPicker')
      .addToUi();
}

/**
 * Displays an HTML-service dialog in Google Sheets that contains client-side
 * JavaScript code for the Google Picker API.
 */
function showPicker() {
  var html = HtmlService.createHtmlOutputFromFile('Picker.html')
      .setWidth(600)
      .setHeight(425)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi().showModalDialog(html, 'Select Folder');
}

function getOAuthToken() {
  DriveApp.getRootFolder();
  return ScriptApp.getOAuthToken();
}

Also save this file as “Picker.html” inside the Google Apps Script project.




  
  



    

The upcoming release of Save Gmail attachments will include the Google Picker API to help users easily select the Google Drive folder for saving the file attachments.

Categories
Code

Make an RSS Feed with Google Apps Script

ThinkAmI uses the XMLService of Google Apps Script to create a valid RSS feed that is served to the browser using ContentService with the MIME type set as RSS. Should be handy for creating RSS feeds for services like the Google Search Scraper that do not natively offer feeds.


/* Credit: https://gist.github.com/thinkAmi */

function doGet() {
  
  var rss = makeRss();
  
  rss.setTitle('RSS 2.0 Feed with Google Apps Script');
  rss.setLink('http://example.com');
  rss.setDescription('RSS 2.0 Feed');
  rss.setLanguage('en');
  rss.setAtomlink('http://example.com/rss');
  
  for (var i=1; i < 3; i++) {
    rss.addItem({title: 'TITLE:' + i,
                 link: 'http://example.com/#' + i,
                 description: 'DESCRIPTION: ' + i,
                 pubDate: new Date()
                })
  }
  
  return ContentService.createTextOutput(rss.toString())
  .setMimeType(ContentService.MimeType.RSS);
}

var makeRss = function(){
  var channel = XmlService.createElement('channel');
  var root = XmlService.createElement('rss')
  .setAttribute('version', '2.0')
  .setAttribute('xmlnsatom', "http://www.w3.org/2005/Atom")
  .addContent(channel);
  
  var title = '';
  var link = '';
  var description = '';
  var language = '';
  var atomlink = '';
  var items = {};
  
  var createElement = function(element, text){
    return XmlService.createElement(element).setText(text);
  };
  
  
  return {
    setTitle: function(value){ title = value; },
    setLink: function(value){ link = value; },
    setDescription: function(value){ description = value; },
    setLanguage: function(value){ language = value; },
    setAtomlink: function(value){ atomlink = value; },
    
    addItem: function(args){
      if (typeof args.title === 'undefined') { args.title = ''; }
      if (typeof args.link === 'undefined') { args.link = ''; }
      if (typeof args.description === 'undefined') { args.description = ''; }
      if (!(args.pubDate instanceof Date)) { throw 'pubDate Missing'; }
      if (typeof args.timezone === 'undefined') { args.timezone = "GMT"; }
      if (typeof args.guid === 'undefined' && typeof args.link === 'undefined') { throw 'GUID ERROR'; }
      
      
      var item = {
        title: args.title,
        link: args.link,
        description: args.description,
        pubDate: Utilities.formatDate(args.pubDate, args.timezone, "EEE, dd MMM yyyy HH:mm:ss Z"),
        guid: args.guid === 'undefined' ? args.link : args.link
      }
      
      items[item.guid] = item;
    },
    
    toString: function(){
      channel.addContent(XmlService.createElement("atomlink")
                         .setAttribute('href', atomlink)
                         .setAttribute('rel', 'self')
                         .setAttribute('type', 'application/rss+xml')
                        );
      
      channel.addContent(createElement('title', title));
      channel.addContent(createElement('link', link));
      channel.addContent(createElement('description', description));
      channel.addContent(createElement('language', language));
      
      
      for (var i in items) {
        channel.addContent(
          XmlService
          .createElement('item')
          .addContent(createElement('title', items[i].title))
          .addContent(createElement('link', items[i].link))
          .addContent(createElement('description', items[i].description))
          .addContent(createElement('pubDate', items[i].pubDate))
          .addContent(createElement('guid', items[i].guid))
        );
      }
      
      var document = XmlService.createDocument(root);
      var xml = XmlService.getPrettyFormat().format(document)
      
      var result = xml.replace('xmlnsatom', 'xmlns:atom')
      .replace('

		
Categories
Code

Authenticate WordPress Users with Google Apps Script

You can use the XMLRPC library with Google Scripts to authenticate users that are members of your WordPress or Blogger website.

For instance, if you have a Google Form that you only wish to serve to registered members of your WordPress or BuddyPress website, you can add a layer of authentication before serving the form using the HTML service. This layer will connect to the website through XML RPC to verify if the connecting user is valid or not.

The Google Script can be further customized to only allow users with certain roles -like allow authors and admin but not subscribers. This can be used in conjunction with Easy Digital Downloads to serve files to only users that have the WordPress role as EDD_Subscriber or similar.

function verifyWordPressUser(username, password) {
  
  // Library key: My_8O8KRa_MszCVjoC01DTlqpU7Swg-M5
  
  /* Replace example.com with your WordPress blog URL */
  var wordpressURL = "http://example.com/xmlrpc.php";
  
  /* Call the wp.getUsers API method to get User details */
  var request = new XMLRPC.XmlRpcRequest(wordpressURL, 'wp.getUsers');
  
  /* The first parameter is empty since there's no blog ID for WordPress */
  request.addParam("");   
  request.addParam(username);
  request.addParam(password);
   
  var response = request.send().parseXML();
  
  if (response.faultCode) {
    throw(response.faultString);
  }
  else {
    throw(response[0].display_name  + " is a valid user");
  }
  
}
Categories
Code

Save Gmail Drafts with Google Apps Script

The Mail Merge Add-on uses the new Gmail API to create and save draft messages Gmail programmatically, something which cannot be done using the standard GmailApp or MailApp service of Google Apps Script. The Gmail API can create draft messages in rich-text which you can generate using the HTML Mail app.

You’ll have to enable the Gmail API under the Google Services console for this code to work. Credit @oshliaer.


// Credit: https://gist.github.com/oshliaer/8db2131bf7357247bc2b

function createHTMLDraftInGmail() {

  var forScope = GmailApp.getInboxUnreadCount(); // needed for auth scope
  var htmlBody = "

Hello, I am an HTML message


"; var raw = 'From: Me \r\n' + 'To: You \r\n' + 'Subject: Save Draft Message\r\n' + 'Content-Type: text/html; charset=UTF-8\r\n' + '\r\n' + htmlBody; var draftBody = Utilities.base64Encode(raw, Utilities.Charset.UTF_8).replace(/\//g,'_').replace(/\+/g,'-'); var params = { method : "post", contentType : "application/json", headers : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()}, muteHttpExceptions:true, payload:JSON.stringify({ "message": { "raw": draftBody } }) }; var resp = UrlFetchApp.fetch("https://www.googleapis.com/gmail/v1/users/me/drafts", params); Logger.log(resp.getContentText()); }
Categories
Code

Get Book Details by ISBN with the Google Books API

The Google Books API can return details of any book from the ISBN code. You get to know the book title, author names, publishing date, publisher and so on.

You don’t need any keys to use the Google Books API and the details are sent in JSON format that can be fetch using Google Apps Script. This can be achieved with the Amazon API as well but would that would take more more since you would need to get the Access keys from the AWS dashboard.

function getBookDetails(isbn) {
  
  // Query the book database by ISBN code.
  isbn = isbn || "9781451648546"; // Steve Jobs book 
  
  var url = "https://www.googleapis.com/books/v1/volumes?q=isbn:" + isbn;
  
  var response = UrlFetchApp.fetch(url);
  var results = JSON.parse(response);
  
  if (results.totalItems) {
    
    // There'll be only 1 book per ISBN
    var book = results.items[0];
    
    var title = (book["volumeInfo"]["title"]);
    var subtitle = (book["volumeInfo"]["subtitle"]);
    var authors = (book["volumeInfo"]["authors"]);
    var printType = (book["volumeInfo"]["printType"]);
    var pageCount = (book["volumeInfo"]["pageCount"]);
    var publisher = (book["volumeInfo"]["publisher"]);
    var publishedDate = (book["volumeInfo"]["publishedDate"]);
    var webReaderLink = (book["accessInfo"]["webReaderLink"]);
    
    // For debugging
    Logger.log(book);
  
  }
  
}
Categories
Code

Make a Copy of Folders in Google Drive with Google Scripts

In Google Drive, there’s no easy way to clone a folder. You can make a copy of individual files but there’s no command for creating duplicate folders that are a mirror of another folder. Fortunately, there’s Google Script to the rescue. The only downside is that the script execution may time out if you are trying to copy a large folder with several sub-folders and files.

function start() {
  
  var sourceFolder = "source";
  var targetFolder = "target";
  
  var source = DriveApp.getFoldersByName(sourceFolder);
  var target = DriveApp.createFolder(targetFolder);

  if (source.hasNext()) {
    copyFolder(source.next(), target);
  }
  
}

function copyFolder(source, target) {

  var folders = source.getFolders();
  var files   = source.getFiles();
  
  while(files.hasNext()) {
    var file = files.next();
    file.makeCopy(file.getName(), target);
  }
  
  while(folders.hasNext()) {
    var subFolder = folders.next();
    var folderName = subFolder.getName();
    var targetFolder = target.createFolder(folderName);
    copyFolder(subFolder, targetFolder);
  }  
  
}
Categories
Code

Post to WordPress with Google Scripts using XML-RPC API

You can use Google scripts to publish blog posts to any Blogger and WordPress website using the XML-RPC API. The script can be extended to create blog posts by email or you can even send a document from Google Docs and publish it your WordPress as a blog post.

The sample code demonstrates how to create a new post. You need to specify your WordPress site’s XML RPC endpoint, the user name and the password in “plain” text. The blog post may be published as a draft or public by changing the post_status parameter. If the blog post is published successfully, the post ID will be returned else it will return an error string.

To get started, do include the XML RPC library in your Google Apps Script project. The project key for the XML RPC library for Google Apps Script is My_8O8KRa_MszCVjoC01DTlqpU7Swg-M5 – choose the latest version from the dropdown and set the identifier as XMLRPC.

function postToWordPress() {
  
  /* Add your WordPress credentials and replace example.com with your WordPress blog URL */
  var wordpress = {
    url: "http://example.com/xmlrpc.php",
    username: "admin",  
    password: "12345"
  };
  
  /* Make sure your WordPress XML-RPC URL is correct */
  var checkConfig = UrlFetchApp.fetch(wordpress.url, {muteHttpExceptions: true});
  
  if (checkConfig.getResponseCode() !== 200) {
    
    throw new Error("Please check your XML RPC URL");
    
  }
  
  /* Call the metaWeblog.newPost API method to create a new blog post */
  var request = new XMLRPC.XmlRpcRequest(wordpress.url, 'metaWeblog.newPost');
  
  /* The first parameter is empty since there's no blog ID for WordPress */
  request.addParam(""); 
  
  request.addParam(wordpress.username);
  request.addParam(wordpress.password);
  
  /* The blog post content. You can have HTML in the description */
  var blogPost = {
    post_type: 'post',
    post_status: 'publish',  /* Set to draft or publish */
    title: 'post title',
    description: 'post description'
  };
  
  request.addParam(blogPost);
  
  var response = request.send().parseXML();
  
  Logger.log(response);
  
}
Categories
Code

Prevent Google Scripts from Exceeding the Maximum Execution Time Limit

Google Apps scripts can run for a maximum period of 4-5 minutes and you’ll get the error “Exceeded maximum execution time” if the script takes more than the allowed time to complete.

The Save Gmail Attachments and Extract Gmail Addresses scripts fetch 50 Gmail threads in a single batch and processes the messages one by one. If any particular thread is long, the overall execution time may exceed the limit and the script may throw an exception.

To prevent the error from happening, inside the loop, we can keep a track of time since the script is running and elegantly stop when it is nearing the time limit.


/* Based on https://gist.github.com/erickoledadevrel/91d3795949e158ab9830 */

function isTimeUp_(start) {
  var now = new Date();
  return now.getTime() - start.getTime() > 300000; // 5 minutes
}

function myFunction() {
  
  var threads = GmailApp.getInboxThreads(0, 50);  
  var start = new Date();
  
  for (var t in threads) {
    
    if (isTimeUp_(start)) {
      Logger.log("Time up");
      break;
    }
    
    // Process the thread otherwise    
    var messages = threads[t].getMessages();
    
  }
  
}
Categories
Code

Backup Web Pages to Google Drive Automatically

This Google Script will take a daily snapshot of a particular web page and saves it as an HTML file in the Google Drive. The code can be extended to backup your native Google Document in standard formats like PDF.

The Google Script by @hijonathan will create daily (or hourly or weekly) backups of any web pages and save it as an HTML file in your Google Drive. You can choose to overwrite the existing file or the backup process can create new copies. The files are saved in date-based folders making it easier for you to retrieve the backup for any particular day.

You’ll have to specify the web page URL and then create a time-based trigger that will run the createBackup() method at specified intervals. Also, only the HTML content of the web page is saved and not the CSS, JS or other associated files.


/* Credit: https://github.com/hijonathan */

var RESOURCE_URL = 'https://news.google.com',
    BACKUP_FOLDER_ID = '',
    FOLDER_NAME_FORMAT = 'yyyy-MM-dd',
    FILE_NAME_FORMAT = "yyyy-MM-dd'T'HH:00:00",

    // Customize your file extension.
    FILE_EXT = '.html',

    // Folder names are all going to look like this.
    now = new Date(),
    FOLDER_NAME = Utilities.formatDate(now, 'GMT', FOLDER_NAME_FORMAT),
    FILE_NAME = Utilities.formatDate(now, 'GMT', FILE_NAME_FORMAT) + FILE_EXT;

function createBackup() {
    var folder = getFolder(FOLDER_NAME);
    createBackupFile(folder, FILE_NAME, fetchData());
}

// Ensures we're always working within the backup directory.
function getFolder(name) {
    var backupFolder = getBackupFolder(),
        folders = backupFolder.getFoldersByName(name);

    if (folders.hasNext()) {
        folder = folders.next();
    } else {
        folder = backupFolder.createFolder(name);
    }
    return folder;
}

// Returns the root folder where our backups exist.
function getBackupFolder() {
    return DriveApp.getFolderById(BACKUP_FOLDER_ID);
}

function createBackupFile(folder, filename, data, overwrite) {
    if (overwrite) {
        // Technically we're not overwriting here. We're just deleting
        // the duplicates.
        var existingFiles = folder.getFilesByName(filename);
        while (existingFiles.hasNext()) {
            var file = existingFiles.next();
            folder.removeFile(file);
        }
    }
    folder.createFile(filename, data);
}

function fetchData() {
    var exportUrl = RESOURCE_URL;
    return UrlFetchApp.fetch(exportUrl);
}

Categories
Code

Gmail to Evernote with Google Scripts

The Gmail to Evernote program will automatically send your Gmail message to your Evernote account using Google Scripts. It reads the various parameters from a Google sheet (like the default tag name and Evernote notebook name) and forwards the matching email threads to Evernote using the GmailApp service.

    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    
    var params = sheet.getRange("D3:D7").getValues();
    
    // Gmail Label name to Monitor
    var label = GmailApp.getLabelByName(params[1][0].trim().replace(/\s+/g, "-"));
    
    var threads = label.getThreads();
    
    for (var t in threads) {
      
      var messages = threads[t].getMessages();
      
        // Forward the latest message in the thread to Evernote
        var message = messages[messages.length-1];
        
        // Append the Evernote notebook and tag to the subject
        var subject = [message.getSubject(), params[2][0], params[3][0]].join(" ");
        
        try {
          message.forward(params[0][0], {subject: subject});
        } catch (f) {
          Logger.log(f.toString());
        }
        
        // Trash the message after forwarding to Evernote
        if (params[2][0].match(/y/i)) {
          threads[t].moveToTrash();
        } else {
          threads[t].removeLabel(label);
        }
      
    }

Categories
Code

Save Google Sheet as JSON

The ExportSheetAsJSON() method will save the currently active sheet of a Google Spreadsheet as a JSON file and saves it to your Google Drive. The header (first row) are used as attributes.


/* From https://gist.github.com/IronistM/8be09ebd4c5a4a58c63b */

function exportSheetAsJSON() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var numCols = rows.getNumColumns();
  var values = rows.getValues();
  
  var output = "";
  output += "{\""+sheet.getName()+"\" : {\n";
  var header = values[0];
  for (var i = 1; i < numRows; i++) {
    if (i > 1) output += " , \n";
    var row = values[i];
    output += "\""+row[0]+"\" : {";
    for (var a = 1;a 1) output += " , ";
         output += "\""+header[a]+"\" : \""+row[a]+"\"";
    }
    output += "}";
    //Logger.log(row);
  }
  output += "\n}}";
  Logger.log(output);
  
  DriveApp.createFile(sheet.getName()+".json", output, MimeType.PLAIN_TEXT);
  
};
Categories
Code

Convert PDF Files to Text with OCR

Google Drive can extract text from regular PDF files as well as scanned PDFs though OCR. SO member Mogsdad has written a wrapper utility for Apps Script that can be used for converting PDF files to Google Documents and it can perform OCR as well. You do need to enable Advanced Drive services from the Google Dashboard.


 /* See gist: https://gist.github.com/mogsdad/e6795e438615d252584f */

  var blob = DriveApp.getFileById(PDF_FILE_ID).getBlob();
  var text = pdfToText(blob, {ocrLanguage: "en"});
  Logger.log(text);


/**
 * Convert pdf file (blob) to a text file on Drive, using built-in OCR.
 * By default, the text file will be placed in the root folder, with the same
 * name as source pdf (but extension 'txt'). Options:
 */

function pdfToText ( pdfFile, options ) {
  // Ensure Advanced Drive Service is enabled
  try {
    Drive.Files.list();
  }
  catch (e) {
    throw new Error( "Enable 'Drive API' in Resources - Advanced Google Services." );
  }
  
  
  // Prepare resource object for file creation
  var parents = [];
  var pdfName = pdfFile.getName();

  var resource = {
    title: pdfName,
    mimeType: pdfFile.getContentType(),
    parents: parents
  };
  
  // Save PDF as GDOC
  resource.title = pdfName.replace(/pdf$/, 'gdoc');
  var insertOpts = {
    ocr: true,
    ocrLanguage: options.ocrLanguage || 'en'
  }

  var gdocFile = Drive.Files.insert(resource, pdfFile, insertOpts);
  
  // Get text from GDOC  
  var gdocDoc = DocumentApp.openById(gdocFile.id);
  var text = gdocDoc.getBody().getText();
  
  // Save text file, if requested
  resource.title = pdfName.replace(/pdf$/, 'txt');
  resource.mimeType = MimeType.PLAIN_TEXT;

  var textBlob = Utilities.newBlob(text, MimeType.PLAIN_TEXT, resource.title);
  var textFile = Drive.Files.insert(resource, textBlob);
  
  return text;
}
Categories
Code

Convert Google Documents and Spreadsheets with Apps Script

You can easily convert any Google Spreadsheet or Google Document in your Google Drive to other formats like PDF, XLS, etc with Google Apps Script and either email the converted file or save it back to Google Drive.

You can get the Email Google Spreadsheet add-on if you prefer the easier route that doesn’t require you to write any Google Apps Script code.


// Credit Stéphane Giron
// Save the Google Document as HTML

function exportAsHTML(documentId){
  var forDriveScope = DriveApp.getStorageUsed(); //needed to get Drive Scope requested
  var url = "https://docs.google.com/feeds/download/documents/export/Export?id="+documentId+"&exportFormat=html";
  var param = {
    method      : "get",
    headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions:true,
  };
  var html = UrlFetchApp.fetch(url,param).getContentText();
  var file = DriveApp.createFile(documentId + ".html", html);
  return file.getUrl();
}

// Credit: Eric Koleda
// Export Google Spreadsheet as Microsoft Excel format

function exportAsExcel(spreadsheetId) {
  var file = Drive.Files.get(spreadsheetId);
  var url = file.exportLinks['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });
  return response.getBlob();
}

Categories
Code

Publish Google Spreadsheets as JSON with Apps Script

The previous snippet on getting Spreadsheets as JSON required you to make a sheet public and also publish as HTML before other apps can pull data as JSON from the sheet.

If you would not like to make your Google spreadsheet public, you can use Google Apps Script to create a web app that will expose the sheet data as JSON.


/* Source: https://gist.github.com/daichan4649/8877801 */
function doGet(e) {

  var sheetName = "Sheet 1";
  var sheetId   = "1234...";

  var book = SpreadsheetApp.openById(sheetId);
  var sheet = book.getSheetByName(sheetName);

  var json = convertSheet2JsonText(sheet);

  return ContentService
          .createTextOutput(JSON.stringify(json))
          .setMimeType(ContentService.MimeType.JSON);
}

function convertSheet2JsonText(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for(var i=0; i

		
Categories
Code

Find Matching Rows in Google Spreadsheets