Zip Codes to City, Region & Geocode in Google Sheets

ZIP codes are a very useful and common way to gather location data. But zip codes alone are often less useful than the city, state, latitude and longitude that correspond to the zip code. City and state/region, allow you to group the data with a pivot table while

City and state/region, allow you to group and aggregate numerical data with pivot tables, while latitude and longitude offer sensible ways to determine areas and distance.

Here is a simple custom function that provides easy access to the zippopotom.us free ZIP code converter API.

 

ZIP Code Custom Function

ZIPLOC( zip code [, country abbrv] )

To translate ZIP codes to geographic info, just supply the ZIPLOC function with a ZIP code. The corresponding two-letter country code is optional but necessary for countries other than the US.

For example, if you want to get geographic data about the 1000 ZIP code of Bulgaria, the function would look like this:

ZIPLOC( 1000, BG)

The function will return the following horizontal array of data:

Country, Region, City, Latitude, Longitude

Bulgaria, София (столица) / Sofija (stolica), София / Sofija, 42.6833, 23.3167

 

Google Apps Script Code

The code makes use of the UrlFetchApp function to make a call to the zippopotam.us RESTful APi and parses the requested JSON data and converts it to an array. To see what the JSON data looks like, check out this URL:

http://api.zippopotam.us/bg/1000

To use the code, just copy and paste it into the Google Apps Script Code editor and click Save. The ZIPLOC function will be available on the corresponding Google Spreadsheet.

 

 

March 8, 2017

Posted In: Custom Functions, Google Spreadsheets

Leave a Comment

Copy Multiple Google Sheets with Template Variables

Business and education administration often involves an overwhelming amount of repetitive tasks. We work in cycles of days, weeks, months, and years with multiple students, clients, employees, or team members. This adds up to multiples of multiple tasks. Luckily, automating repetitive and work-intensive tasks is just one of the many things Google Apps Script was made for.

In this Google Apps Script example, let’s automate all but the individualized aspects of working with Google Spreadsheets. We will create a template sheet and make multiple copies of that sheet with individualized variables for each sheet.

With a click of a button, this script will copy multiple versions of a sheet for each time period, each project, or each person.

 

How to Use the Script

Using the script is just as easy as using any other. Just copy the code below and paste it into the Google Apps Script code editor. For a working example, make a copy of this demo sheet.

  • Save the script
  • Run the onOpen() function or refresh the spreadsheet to instantiate the drop down menu
  • Select Create template variables sheet if the Template Variables Sheet does not already exist. 

Google Spreadsheet template copy

  • Fill in your template variables. The template variable names can any combination of letters, numbers, and underscores (_) enclosed with curly braces  eg: {{1st_place}}

Google Spreadsheets template sheet copy

  • To add more template variables, click Add Template Variable. To remove template variables, just delete that template variable’s column.
  • Before you make copies, ensure that the template variables sheets are completely filled out, including the {{sheet_name}} variable (the name of the tab). Else the variables will be left blank. Also, make sure that all cells outside of the template variable names columns are left empty.
  • To create copies, click the Create copies on the Sheet drop-down menu. This will create a new copy of the template sheet with unique variables for each row of template variable definitions.

 

How The Script Works

There are three parts to this script: the menu, the template variables sheet, and the sheet copier.

The menu follows a common and obvious pattern; it is built by the onOpen function. The onOpen function is called every time the spreadsheet is opened so whenever you open the spreadsheet, the menu is ready to use. Learn more about menus in Google Apps Script.

To make it as easy as possible for the user to avoid error, there is a function called createTemplateVarsSheet() that adds a sheet labeled “Template Variables” and adds an example header row and an example variable row. This function is called whenever the user clicks Create template variables sheet on the menu.

With the same intent to avoid any user error, the function addTemplateVariable() adds a new template variable column to the template variable sheet. This is can be done manually quite easily, but this function, called when the user clicks Add template variable, walks the user through the process and keeps the same consistent formatting or the Template Variables sheet.

The real value of this script comes in the templateCopier() function. This function iterates through each row of the Template Variables range and creates a copy of the template sheet with the values for each row.

The magic happens in a call to the replaceTags() function. This function is called in every cell of the copied template sheet and uses a regular expression to find and replace a template tag with its corresponding value in for the copy of the template.

Once every cell in every copied sheet has been updated the script is done and just like that, there are a number of new replica sheets.

Here is the script:

 

Google Spreadsheet Copier

I hope you find it useful for automating your work in Google Spreadsheets. If you think it could be improved to serve your purpose, let me know in the comments.

March 7, 2017

Posted In: Google Spreadsheets

Leave a Comment

Google Spreadsheet Button to Run Scripts in 4 Steps

 

If you come from Excel, you might have seen some fancy spreadsheets with clickable buttons that trigger VBA macros. Luckily, Google Spreadsheets and Google Apps Script offer the same functionality.

This tutorial will demonstrate how to add custom buttons to a Google Spreadsheet that run Google Apps Script functions when they are clicked. There are also examples for buttons that sort ranges, add timestamps, send emails, and increment cell values.

 

Let’s Make a Spreadsheet Button

Creating a spreadsheet button is actually easier than creating a custom UI menu. There are just three steps:

 

1. Create Your Custom Button

Image for Google Spreadsheet Button

There are two ways to create a button in Google Spreadsheets. You can either insert an image or insert a drawing. For this example, we will insert this amazing image of a Gorilla high-fiving a Shark. (I bet you’re glad you picked this tutorial now.)

 

 

 

Google Spreadsheet Insert Button

In the Google Spreadsheet top menu, go to Insert > Image or Insert > Drawing.

There are several ways to embed images. You can upload them or add them by URL. That’s how I found this great example.

Creating a drawing is easy. Just make a shape that looks like something you might want to click and click Save & Close. The benefit of drawing a button is that you can write descriptive text for the button to signal what the function does.

 

Now that you have a button image on your spreadsheet. Let’s write the function that it will trigger.

 

2. Create Your Custom Function

For this example, we are going to keep it simple. This custom function, called highFive, simply displays a pop-up box that says “High Five!” Dont’ worry, we will get into more interesting functions later in the tutorial.

Go to Tools > Script Editor and define and insert this function.

That was easy. Now in the Google Apps Script menu and save your script file: File > Save.

 

3. Assign Your Function to you Button

Assign Script to Button

 

Right-click the image or button and at the top right corner, there will be a small drop down menu. Click Assign script…

 

 

 

 

Name the Google Apps Script Button Function

 

 

You will be prompted, “What script do you want to assign?”

Enter the name of the function that you will use. In this case highFive.

 

 

 

4. Click the Button!

And now for the grand finale! Click the button and you will see the High Five Button Click!message box say “High Five!”

If you need to reposition your button, right click the image. The button will then be outlined in light blue and you can drag the button to wherever you might need it.

Placing the button in a frozen row is also a good idea in case you are using a big spreadsheet.

 

More Useful Functions for Buttons

There are many more useful ways to use Google Spreadsheet buttons. Here are a few.

 

Set a Cell Value to the Current Date and Time

If you are using a spreadsheet to keep track of times, you might need to know exactly what time you started or completed a task. In that case, you can make your button trigger a function that sets the value of the active cell to the current time.

 

Send an Email to a Specific Recipient

Using a button is a really convenient way to run a function that takes spreadsheet values as arguments. The following code will allow you to send the contents of the active range as the email address, email subject, and email body.

Email Button From Spreadsheet

Highlight the three cell range, click the button, and your email is on its way. Of course, you can further customize the function with the recipient’s name, for example. But that is for another tutorial.

 

Increment a Cell Value

Sometimes it’s just easier to click buttons than edit the value inside of a cell. This function named increment, will add one to the value of cell A1 every time it is clicked.

 

Sorting a Range

This would be a great functionality to attach to a button click. However, the sort() function seems to have a known bug. Here is how to do this according to the Google Apps Script documentation.

 

Last Notes

Unfortunately, you can not insert the button inside of a cell. The button will always be effectively floating above the spreadsheet. But as I said before, you can right click and drag the button around a freeze the row that the spreadsheet will stay on to keep the button in a static position.

I hope this has helped you understand how to use Google App Script buttons in Google Spreadsheets and maybe even given you some ideas. If you are still looking for another example, tell me in the comments. If you have an interesting use for Google Spreadsheet Buttons, share it in the comments as well!

 

February 9, 2017

Posted In: User Interface

Tags: ,

Leave a Comment

How to Get, Parse, Query, and Return JSON

 

JSON (JavaScript Object Notation) is a great way to pass data around the Web. It is easy for humans to read and it’s easy to work with in most programming language (including Google Apps Script).

This post will show you a number of useful examples of how to work with JSON with Google Apps Script.

 

(re)introducing JSON

Douglas Crockford Proposed JSONJSON refers to a syntax for organizing information in text, a MIME type, and a file type. They are all part of one big idea from Douglas Crockford (the guy in the picture).

JSON Syntax has two structures: objects, key-value pairs; and arrays, lists of values. The values can be strings, numbers, true, false, null, arrays or objects. For more on the JSON syntax, check out json.org.

The JSON MIME or Media Type, is expressed as application/json for the HTTP Content-Type header. You might see it’s more opinionated, but equivalently parseable brother, application/vnd.api+json.

The JSON file type always has the extension .json and is essentially a text file that organizes information in the syntax mentioned above.

You may have heard people refer to pretty-printed JSON. This term describes JSON text when it is written to a file or printed to a screen so that it is human readable. Pretty-printed JSON typically uses a lot of white space and displays one value per line. JSON is not always pretty printed because these additional spaces and linebreaks slightly increase the size of the file.

Now let’s get some JSON and make something happen!

 

Getting JSON with Google Apps Script

Getting JSON into your Google Apps Script code is just like getting JSON into any other code, you either access it from your file system (in this case Google Drive) or retrieve it from the internet.

 

Downloading JSON from an API with UrlFetchApp

APIs are probably the most entertaining way to get JSON. For this example, we will use the Chuck Norris Quote API. Calls to the http://api.icndb.com/jokes/random API endpoint return JSON that looks like this:

(This JSON is pretty printed but the API does not actually return pretty-printed JSON)

To get request JSON from this API, all you have to do is:

We will see how to parse and query this later.

 

Receiving JSON with doPost

If you want to POST JSON from your computer or another cloud application, you can use the Google Spreadsheet Web App feature to receive the data. From there, it is quite easy to save that JSON text to a file in Google Drive.

The doPost() function runs anytime the web app receives a POST request. It takes one argument. That argument can be named anything you want but it is an object that stores information about the request. In this case, we access the string representation of the JSON data from the request parameter.

To test this little script, you can use Postman to send a POST request with raw JSON data. Or you can copy this into your (Mac) terminal.

When the above POST request is made, a file named post.json will be created in Google Drive with the contents:

 

Accessing JSON Files From Google Drive

Let’s continue from the example above. If you wanted to access the file called post.json from Google Drive,  you can then read the file in as JSON and work with its contents in your script.

 

Parsing and Querying JSON with Google Apps Script

If you have any experience with Javascript, this should be a walk in the park. But in case you are new to it, let’s take a look at the JSON Google Apps Script Class.

The JSON Class offers two different methods for working with JSON. JSON.parse() reads in the JSON text into a Javascript object, and JSON.stringify() serializes (a.k.a. converts) Javascript objects into JSON text strings.

 

Parsing JSON with JSON.parse

As we saw above, when you read in or receive JSON, it will be represented as a string. But in order to use that within your script, you will need to convert it into an object. That is really easy to do. Let’s use the Chuck Norris JSON to examine this.

Now we have Javascript object that we can use in our script.

 

Querying JSON

Querying just means looking something within a data source. In our case, we are less querying than JSON than a Javascript object, but you get the idea. Following from the example above:

Notice, that with Javascript you can access object properties with either “dot notation” or “bracket notation.” Dot notation is convenient but bracket notation is useful when you need to access an object’s property with a variable rather than a string.

Arrays can only be accessed with bracket notation. Note that the index for the array starts with zero and if an array doesn’t have a specified index or an object doesn’t have a specified property, the result will be undefined. JSON uses null, but undefined is the Javascript equivalent.

 

Creating JSON with Google Apps Script

Now that we have seen a few ways that we can get JSON, let’s see a few ways that we can create it. When you have a Javascript object, serializing JSON is very easy:

To provide a user or another application we can reverse the methods that we used to get JSON above.

 

Return JSON with doGet

doGet, similar to doPost, responds to requests to your web app. To return JSON to a GET request, we simply handle the request with doGet, then return JSON text with the ContentService.

The ContentService class provides a set of utilities to create and define several types of output, including images, HTML, and of course, JSON. Whatever the doGet function returns is what is returned the client that makes the request.

 

Send JSON with UrlFetchApp

Earlier, we saw how to receive POST requests and accept the JSON payload data. Here we see how to send the POST request using the Google Apps Script UrlFetchApp function.

The second argument for UrlFetchApp.fetch is an object the specifies the details of the request. In this example, we specified a POST requests with the JSON content-type header and the JSON data from the example above.

 

Save a JSON file to Google Drive

This was show above, but since you’re here, let’s look at it again. To save a JSON file to Google Drive, serialize the Javascript object to text, and use the DriveApp class to save the file to Drive. Again, we will use the JSON from myDog object above.

Yes, it is that simple. Just search for “rhino.json” in Google Drive and you will see a file with that name. You can also dynamically name the file by passing in a variable to the first argument to DriveApp.createFile.

 

Printing a JSON data to Google Spreadsheets

One of the best things about Google Apps Script and Google Spreadsheets is that it is so easy to get data from APIs into spreadsheets. Here’s an example to create a spreadsheet header with the object keys and set the row values to the values of the object.

If you have an array of multiple objects, you could run them all through a loop to populate the contents array and then write the values of all the objects to the spreadsheet.

{“JSON”: “all the things!”}

I hope this offers you confidence to work with JSON in your Google Apps Script future. If I missed anything or any of the examples or instructions were unclear, please let me know in the comments. Also, feel free to share your JSON project in the comments! Happy scripting!

February 7, 2017

Posted In: Programming & Data

Tags: , , ,

One Comment

Google Apps Script Custom Menus

 

It is fitting that my first post on this site is about custom menus with Google Apps Script, because, often, custom menus are your users’ first touch with your custom functionality. Your users will be surprised the first time they see the custom menu on their Google App UI. And they will be delighted when the functionality fulfills their expectations. This post will show how to surprise and delight your users with the Google Apps Script UI menus. 

 

Google Apps Script UI Menu

 

 

How Custom Menus Work

Custom menus allow you to execute the Google Apps Script functions that you have defined. Each menu item, when clicked, triggers the Google Apps Script function that corresponds to that menu item. Custom menus are easy to create, and we will get to that in a minute, but let’s get a few questions out of the way first.

Menus can be created and modified*, but menus cannot be deleted.

Most often, Menus are created with the onOpen() function, but they can also be created by an ay trigger or event. For example, you could use a menu item click to create another menu. Similarly, many Google Docs add-ons use the add-on menu to create a menu to offer access to the functions of their add-on.

Unfortunately, Menus cannot be dynamically created. The menu item names can be, but the functions that the menu items trigger can not be. This will become clear later, but the functions that are called by each menu item must be defined in advance and cannot take arguments. If you want to call functions with dynamic inputs, use the PromptResponse class or the value of the active cell if you are using Google Spreadsheets.

Menus can be modified in the sense that they can be replaced with an updated instance (copy) of the same menu. Any time the .createMenu() is used to create a menu with a name that has already been used, the .addToUi() method will then replace that original menu with the new menu.

 

Let’s Make a Custom Menu

Creating custom menus is very easy because the code that creates reads exactly like the menu appears. Each chained method gets a new line to represent the menu feature that the method adds.

 

Create Custom Google Apps Script Menus

 

To create a menu that appears when the Google Spread or Google Docs application is opened, place the call to .createMenu() inside the .onOpen method.

 

The Ui Class and the Menu Class

Custom menus make use of two Google Apps Script classes. The Ui Class offers the ability to add to, and modify the user interface of the Google App that the script is bound to. This is everything from pop-up prompts and alerts, to sidebars, to of course, menus. The Ui class offers the only way to create a Menu object, with the .createMenu() method.

The Menu class is much smaller in scope. The Menu class only offers the ability to define the appearance and functionality of a menu, and that is all.

If you find yourself creating big menus with sub menus, you might want to define each menu individually and pass them to their parent menu, in the .addSubMenu() method. For illustration purposes:

 

Three tier menus are actually not a good idea. Here’s why:

 

Tips on User-Friendly Menus

At a minimum, menus should be easy to use and fast to load. For usability, Google’s Material UI Guidelines, recommends the following:

  • Menu names and menu item names should be short, descriptive, and accurate, with sentence casing.
  • Do not duplicate menu names or menu item names
  • Menus should be nested no more than one level deep. (The example above was for example purposes.)

 

Google Apps Script Menus Hello World

 

Get started! To see how these menus work. Copy and paste one of the scripts into the Google Apps Script editor and press play. I hope this helps keep the users of your custom Google App Script happy!

 

February 3, 2017

Posted In: User Interface

Tags: ,

One Comment

doGet and doPost Tutorial + 6 Web App Examples

Google Apps Script Web AppsIf you are new to Google Apps Script or programming in general, Google Apps Script Web Apps may seem daunting. The good news is that it is easy to understand with a little trial and error.

This tutorial will give you 12 recipes to help you get started with the doGet and doPost functions and show you how to return HTML, JSON, and plain text. From there, you can go on to customize your Web App to your needs.  But first, let’s get the basics out of the way.

 

What do doGet and doPost do?

Since you are starting to build a web app, you should probably know about HTTP. HTTP is the specification for how different machines on the web send and receive information from one another. HTTP allows several types of “requests,” but what we see most often are GET and POST requests. doGet and doPost functions define how a Google Apps Script web app scripts handle GET and POST requests from other machines and determine what they return or do.

A simple example of a GET request happens every time you enter a URL into your web browser. The web server at that domain then handles your request and returns an HTML web page. GET requests are the most common type of request on the internet. For our purposes, GET requests are made of up of a domain, e.g., example.com a path, e.g., /fruits/apples/ and often a query string e.g.,?variety=fuji&count=4With Google Apps Script, the domain will always be script.google.com, and the path will always look with something like /macros/s/AKf...ycb/exec (or /dev), and the query string can be whatever you want it to be, in order to specify how the web app dynamically generates the content it returns.

POST requests are similar to GET requests but slightly more powerful. Rather than asking for content, they are used to send data from one machine to another. The most common use of POST requests is web forms, like signup forms and ecommerce checkout pages. POST requests are not limited to sending form text though. They can transfer image, audio, and a number of other types of files. This tutorial will only cover JSON, but if you need more than that, you probably don’t need this tutorial!

 

Your First Web App “Hello World”

Let’s get a few easy examples out of the way. The next three code snippets serve essentially the same content in three different formats.

To try them out:

  1. Copy and paste them into a new Google Apps Script.
  2. Click Publish on the main menu and then Deploy as web app…
  3.  Click “Test web app for your latest code.”

 

Returning Plain Text

The ContentService.createTextOutput method is the most basic way to return content from a GET request but is the building block for most other types of content. The TextOutput objects created by this method returns a response of type, text/plain by default, but can be set to ATOM, CSV, ICAL, JAVASCRIPT, JSON, RSS, TEXT, VCARD, and XML.

 

Returning JSON

This is a simple pattern. Just serialize the Javascript object as JSON using JSON.stringify(), and set the content-type header with the setMimeType() method. This is important when returning different media types because you don’t want to let the recipient of your request guess the MIME type. For more on this, see my Google Apps script JSON REST API tutorial.

Returning HTML

The HTMLService.createHtmlOutput() method has two purposes: to clean of any security risks before the HTML is returned to the browser and to implicitly set the MIME type to text/html. To construct web pages, you can assemble the HTML string, as shown below, or you can use templates.

 

Dynamic Web Pages with the HTML Service

 

External Content, Query Strings, and HTML Templates

Let’s make this interesting. Google Apps Script can assemble web pages based on dynamic inputs. In the example below, we use an external CSS file and the query string parameters to create dynamic content. The doGet and doPost parameter take one default argument (commonly e), which holds information about the request “event.” From that e object, you can pull of information like query string values, as shown below. See the working version of the web app here.

To take this one step further, you can create HTML template files in Google Apps Script and evaluate those templates rather than creating the template in the script. Additionally, you can use information from the Google Drive, Gmail, and other Google APIs to populate your app.

 

Google Apps Script doPost Example

 

An API Proxy

This example was inspired by my last post about custom Slack slash commands. This doPost example demonstrates how to handle POST requests, access request query string parameters, make HTTP requests to other services (in this case, the Chuck Norris joke API) with UrlFetchApp, and break the script into functions.

The reason I made this was because I got data from the Slack app that was formatted as a query string,

that I needed to use to get data from an API that would be formatted as JSON,

to then send back to Slack app as plain text:

This problem also exists if you want to use another service that requires that data be formatted in a specific way- like the IFTTT Maker Channel.

 

More than Just Returning Content

 

Dynamically Create and Save a PDF File to Google Drive

This example is similar to the dynamic HTML example above but instead of making a dynamic web page, it creates and saves a PDF document in Google Drive. This would also work with doPost in a situation where you would want to send more information to the app when creating you PDF. Check out the live script. *Note, this will create a PDF file in you Google Drive each time you go to the URL.

This takes the functionality of the Google Apps Script web app one step further. This script demonstrates the capability to carry out any task or trigger any other service just from GET’ing or POST’ing to your web app.

 

 

Publishing and Permissions

When you are ready to launch your new Google Apps Script web app. You should save a version and define who and how users and other machines can access your app. There are a couple important things to note.

If your app offers any capability to interact with other Google Drive services, the app should be set to Execute the App As:  User accessing the web app.

If you want to allow other services, to interact with your web app (as an API or proxy, the app should be accessible to Execute the App As: Me (myemail@gmail.com) and allow Anyone, even anonymous.

 

I hope this gets you started! Let me know if you are having any trouble getting your app up and running in the comments. Happy hacking!

January 30, 2017

Posted In: Web App

Tags: , , ,

Leave a Comment