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

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

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.

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

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.

Convert and Email Google Spreadsheets as PDF Files

You can setup 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 schedule.

This example shows how to send a Google Spreadsheet to the specify email address automatically as a PDF file daily. The Google Script converts the Google Spreadsheet into a PDF file and send them to any email address using the MailApp service. You can further customize the PDF output – like remove gridline, hide frozen rows, change to landscape mode, etc. by setting the correct export parameters.

If you require customization or need help to install the script in your Google Drive, please contact me via the contact form at ctrlq.org.

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 a first sheet, you can set the gid=0 and so on.

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.

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.

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.

Search Files inside Sub-folders in Google Drive

Like the previous script for listing Google Drive files, this Google Script will find all files of any particular MIME type in your Google Drive.

Unlike the DriveApp.getFilesByType() method that will only search for files in the immediate folder, this Google Script will also search for files inside the sub-folders.

Reorder Worksheets in Google Spreadsheets by Name

You have a Google Spreadsheet with multiple worksheets and, to making organization easier, you would like to reorder or sort the various sheets inside the spreadsheet by name.

Google Apps Script to the rescue. Paste the snippet inside the Google Sheet’s script editor and run the sortGoogleSheets method from the menu.

Export Tweets from Twitter with Google Apps Script

The Twitter Archiver app will archive tweets for any hashtag to a Google Spreadsheet using the Twitter API and Google Apps Script.

You can set a time-based trigger to run downloadTweets() every 5 minutes or even 1 minute for #hashtags that are extremely popular and generate thousands of tweets. The code has been updated to using the OAuth1 library instead of the OAuthConfig service which has since been deprecated.

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.

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.

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.

Save Gmail Drafts with Google Apps Script

The sample Google Script uses the new Gmail API to save a draft messages inside your Gmail mailbox, something which cannot be done using the regular GmailApp or MailApp service of Google Apps Script. It create HTML messages.

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

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.

Keep Google Scripts within 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.

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.

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.

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.

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 export it back to Google Drive.

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

  • Most Useful Google Scripts by +Amit Agarwal
  • Google Scripts for Google Apps – a collection on Google+
  • Case Studies – Sample Google Script Projects (google.com)
  • Google Apps Scripts – Snippets
  • scoop.it/t/gas by +Martin Hawksey
  • O’Reilly – Apps Script – by +James Ferreira
  • Apps Script Webinars – YouTube – by +Eric Koleda
  • scriptsexamples – by +James Ferreira +Romain Vialard
  • StackOverflow Q&A – Got a question? Ask here.
  • lynda.com – Scott Simpson (Up and Running with Google Scripts)
  • Tutorial – Written Google Docs Addons
  • Known Issues & Bugs – Report a bug here.
  • This list is maintained by Amit Agarwal. Send me a tweet @labnol to suggest another resource.

    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.

    Upload Files to Google Drive with Google Apps Script

    Google Forms do not support file uploads but one can use Google Apps Script for uploading files to Google Drive through an HTML web form.

    First you need to create a form using standard HTML, CSS and JavaScript. You can use the standard CSS package of Apps Script to give the standard Google look-n-feel to your form. Here’s a sample form:

    The server side 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, extensions and content type are preserved.

    You can save the two files and deploy the script as a web app as demonstrated in the GIF animation below.

    A similar technique is used for hosting podcasts and websites on Google Drive.

    Upload Files to Google Drive

    Find Matching Rows in Google Spreadsheets

    You have a Google Spreadsheet and you need to programmatically find if a cell with particular value exists in that sheet. Well, Google Scripts can help. You can either search cells in a single column (like A) or the script can search for all cells in the row and return the index of the matching row.

    See your Fitbit Data in Google Spreadsheet

    The Google Script will download your Fitbit data via the Fitbit API and insert it into a Google spreadsheet. The first row of the spreadsheet will be a header row containing data element names like steps walked, body fat, calories burned, etc. Subsequent rows will contain data, one day per row.

    This is a Google Spreadsheet bound script so you need to create a sheet first and put this code inside the Script editor. Fitbit uses metric units (weight, distance) so you may wish to convert them as per your locale.

    Publish to Twitter with Google Scripts

    You can easily post a tweet to Twitter from Google Script much like the Twitter bot DearAssistant that internally uses the Wolfram API. You’ll have to setup a Twitter app with Read/Write permissions and use the consumer keys and secret here.

    How to Merge Multiple Google Documents

    You can use Google Apps Script to merge two or more Google Documents into a single document. The script takes the first document and appends the content of all the other documents into this document. All the formatting, tables, lists and other elements are preserved in the merged document.

    Update: [Merijn Peeters] My document included a very big table, and when merging several of those documents, a blank line was added from the second page onward. This corrupted the layout, of course.

    After hours of searching, I discovered that the error was due to the fact that the ‘appendTable’ function automatically appends a blank paragraph as well, because a document cannot end with a table. From Google’s documentation:

    appendTable() – Creates and appends a new Table – This method will also append an empty paragraph after the table, since Google Docs documents cannot end with a table.

    Google URL Shortener with Google Scripts

    You can use Google Apps Script with the Google URL Shortener API to convert any long URL into a short one served through the goo.gl domain. Make sure you replace key with your own key from the Google Console dashboard. You can also bit.ly to shorten URLs.

    The other easier alternative for creating short URLs with the Google URL shortener API doesn’t require you to create a key as it passes the OAuth 2.0 access token for the current user in the header.

    Google Maps Functions for Google Spreadsheets and Apps Script

    The Maps Service of Google Apps lets you reverse-geocode and geocode postal addresses without using the Google Maps API. Given the latitude and longitude, you can get the link to the place on Google Map, it can get the formatted street address with City & zip from the geo coordinates or vice-versa. Credit: @thomasfr

    These geo-functions can be used inside Google Spreadsheets and also Google Scripts as in the Twitter Archiver that puts the geo-tagged tweets on a Google Maps.