Goal Seek in Google Sheets

Goal Seek for Sheets is an Add-On for Google Sheets for doing Goal Seek type data analysis.

In October 2019, Google launched an official Add-On, called “Goal Seek for Sheets”, and it is that Add-On that this tutorial references.

1. What is Goal Seek?

It’s a tremendously powerful and useful technique in data analysis. It’s a process where you set an output you want to achieve (e.g. break even, sell 10k units, save $1m) and let the computer find the input value that will get you there (e.g. 500 attendees, $100k capital lump sum, save $8k/year).

There are three components: 1) the unknown input variable, 2) the equation or calculation that is performed on the input variables to get the output, and 3) the known output.

The Goal Seek algorithm performs a series of “what-if” calculations by plugging in different input values. Each guess (hopefully) gets closer and closer to the solution.

For example, a classic use case of Goal Seek is to determine the number of sales required to break even, given other variables like fixed costs etc.

2. How do you use Goal Seek in Google Sheets?

Imagine Jennifer runs an annual conference for Google Sheet developers called “Sheet Freakz 🤟”.

She has a great venue picked out with room for 500 and she’s confident she can fill it. She knows what her costs are — the rental fee for the room, the cost of catering, the cost of promoting the conference — and she has agreed a $1,500 fee with 15 Google Sheets experts to come and talk about the latest and greatest in Sheets developments.

(Editor note: I wish this was a real conference!! 😄)

What price must she charge to cover her costs?

This is a classic break even cost analysis example that the Goal Seek Add-On is ideally suited for solving.

Setting up the Sheet

The first step is to simply add all of the known variables into a sheet, like so:

Goal Seek Variables in Google Sheet

These are the variables that Jennifer knows at the start of her problem.

Next, add a line for the registration fee per attendee, but set it to 0 for now. I’ve highlighted the cell yellow to indicate that it’s the solution cell that I want Goal Seek to solve for:

Goal Seek Variables

Finally, add a profit line, which is my revenue (# of attendees * registration fee) less expenses (fixed costs + (# of speakers * speaker fee)):

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ) )

Goal Seek setup

Of course, initially, my profit is -$47,500 because I have no attendees and hence $0 revenue.

It’s time to use Goal Seek and let it find the break even registration fee for us.

How do you add Goal Seek in Google Sheets?

Goal Seek is an Add-On, which means you need to add it to your Google Sheet before you can use it.

Search for “Goal Seek” in the Add-Ons marketplace, found under the menu Add-ons > Get add-ons

The official Google Add-On information page will appear.

Click to install. That’s all it takes to add it to your Sheets.

Goal Seek for Sheets

You can also find it in the G Suite marketplace directly by clicking here.

Using Goal Seek

Open the Goal Seek sidebar: Add-ons > Goal Seek > Open

There are three pieces of information you need to enter:

i) Set Cell

Jennifer wants to know what price to charge to break even. In other words, what’s the minimum ticket price to ensure her profit is $0 and she doesn’t lose any money on the conference.

The “Set Cell” is the one we want to specify a value for. It’s the target we’re aiming for.

It’s the cell with the calculation formula in.

** With the Goal Seek sidebar open, click on the cell with the formula, which is cell B10 in this case (1). Add that reference to the Goal Seek solver by clicking on the grid icon next to the Set Cell box (2). This will auto-populate with the cell B10 reference. **

Select cells in Goal Seek for Sheets

ii) To Value

Next, type in the value of the output you want to achieve in the “Set Cell” box.

In this example, we want to set the profit value to 0, so we simply type 0 into this input box.

iii) By Changing Cell

What input variable do we want to vary to solve our equation?

In this case it’s the registration fee. It’s the variable that Jennifer is trying to find, such that her profit is $0 and she breaks even.

Select the cell that holds this variable and then click on the grid icon next to the input field to auto-populate it.

Read it out loud to understand what you’re asking the application to do: “Set Cell X To Value Y By Changing Cell Z”.

In our case, “Set Cell Profit To $0 By Changing Cell Registration Fee” or even cleaner “Set Profit To $0 By Changing Registration Fee”.

When you have all three inputs filled in for the Goal Seek application, the Solve button will turn blue and become active.

Press it.

The registration fee value will start jumping around all over the place as the computer tries different guesses to see what brings the profit value closer to 0.

Eventually it’ll find a solution and notify you that it’s done!

Goal Seek to determine break even cost

In this example, it’s found that the break even registration fee is $94.99999 dollars, or $95. Great!

Click here to open a Google Sheet template with all the examples from this tutorial >>

(Feel free to make your own copy: File > Make a copy…

If the file won’t open without permission, please open in an incognito window and copy from there.)

Manual Checks

It’s always a good idea to check the final solution that the Add-On finds, and not just trust it blindly.

In our example, it’s very simple to check that the two sides of the equation balance.

Jennifer’s expenses to run the conference are:

Expenses
$25,000 fixed costs + ( 15 speakers @ $1,500 each ) = 25,000 + ( 15 * 1,500 ) = $47,500

And her revenue on the other side of the equation will be:

Revenue
500 attendees * $94.99 registration fee (Goal Seek solution) = $47,499.99

The difference is simply a rounding error.

This is good.

The result from the Goal Seek is indeed a solution that gets Jennifer the break even registration price.

Another way to look at how the Goal Seek solver works is to visualize it. A very simplified version might look something like this:

Attempt 1

The computer has no idea what the solution is, so it makes a guess. For example, it might overestimate the result:

Guess 1: Overestimate

Attempt 2

The computer makes another guess. This time it might underestimate the result:

Guess 2: Underestimate

(It won’t always be a neat over/under/over/under guess. For example, if it guesses low, it might take many guesses before the first “over” guess happens. So this over/under flow is just to illustrate the concept.)

Attempt 3

With each additional “guess” the computer gets more accurate, because it uses the information from prior guesses to get closer to the solution. It might still overestimate, as shown here, but it’s getting closer to the solution:

Guess 3: Overestimate

Attempts 4 onwards

So on and so forth, as the computer makes guesses that get closer and closer, under, over, under, over, under, over, etc. until the solution is found:

Converging on solution

The program converges on the solution.

3. Other features of the Goal Seek Add-On

These features are all found in the sidebar underneath the input section for the Goal Seek variables.

Options

Under the Options menu, you can adjust the default settings for the Goal Seek solver.

You can change the 1) max number of iterations, 2) the tolerance (how accurate you need to be) and 3) the maximum time limit for the process, in seconds.

Goal Seek Settings

I’d suggest that the default settings will suffice for the majority of scenarios, but it’s good to know that you can make these changes should you need to.

Solve Status

The Solve Status box displays helpful information about the current Goal Seek solution.

It lets you know when the algorithm is finished, what the final status is, how many iterations it required and how long it took.

Solve Status in Goal Seek for Sheets

History

Access previous runs of the Goal Seek solver under the History menu.

Use the drop down menu to choose a prior solution based on a timestamp.

Goal Seek History

Error Messages

Occasionally the Goal Seek solver fails to find a solution.

One reason might be that the computer guesses get successively further away from the actual solution. They diverge away from the solution.

Should this happen, you’ll see an error message like this:

Goal Seek error message

4. More Goal Seek examples

Conference Break Even Example For Attendee Numbers

In the conference example above, instead of knowing how many people would attend, suppose Jennifer knew the registration fee.

She charges $299 for the registration fee and wants to know how many attendees she requires to break even?

The “changing cell” in this case becomes the number of attendees, rather than the registration fee.

The setup would be:

Goal Seek conference Example 2

The formula in cell B10 does not change from the original example. It’s still:

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ))

The Goal Seek settings are:

Settings for conference example 2

Run Goal Seek and the answer comes back as 158.86.

In other words Jennifer needs 159 attendees paying a registration fee of $299 to break even.

Mortgage Calculation Example

Suppose you’re looking to buy a new house. You have an upper limit for your monthly payments of $1,500.

The other known variables in this case are: it’s a 30 year term with an annual interest rate of 4.5%.

What’s the maximum amount you can borrow?

Goal Seek can solve this for you.

Firstly, setup the sheet with the known variables in your Sheet:

Mortgage Goal Seek Example

The payment equation uses the PMT function in Google Sheets in cell B8:

= -PMT( B6 / 12 , B5 , B7 )

In Goal Seek, set the “Set Cell” to be this equation in cell B8.

Set the “To Value” to $1,500 (the maximum monthly payment that can be tolerated).

Set the “By Changing Cell” to the Amount Borrowed in cell B7 (currently $0.00).

Click Solve and let Goal Seek find your solution.

The algorithm will churn through the possible solutions until it settles on one that satisfies your tolerance (accuracy) setting.

In this case, the maximum amount you could borrow is $296,041.75.

Mortgage Solver Solution

Retirement Calculation Example

Suppose you want to retire with a pot of $1.5m in 40 years time. You’re confident of getting a 5% return on your investments.

What’s the annual contribution you need to make each year to hit this target?

Let’s use Goal Seek to find out.

Firstly, setup the sheet with the known variables in your Sheet:

Retirement Goal Seek Example

The calculation of the retirement pot value uses the Future Value function, the FV function, in Google Sheets in cell B7:

= FV( B5 , B4 , -B6 , 0 , 0 )

In Goal Seek, set the “Set Cell” to be this equation in cell B7.

Set the “To Value” to $1,500,000 (your target retirement pot).

Set the “By Changing Cell” to the Annual Contribution in cell B6 (currently $0.00).

Click Solve and let Goal Seek find your solution.

In this case, you need to contribute $12,417 each year to hit your retirement pot target of $1.5m.

Retirement Solution

Click here to open a Google Sheet template with all the examples from this tutorial >>

(Feel free to make your own copy: File > Make a copy…

If the file won’t open without permission, please open in an incognito window and copy from there.)

Resources

Google Documentation on the Goal Seek feature.

How to connect the Strava API with Google Sheets and Data Studio

This post looks at how to connect the Strava API with Google Sheets and create a visualization in Google Data Studio.

Strava api with google sheets and data studio

Strava is an insanely good app for athletes to track their workouts. I use it to track my running, biking and hiking, and look at the data over time.

This whole project was born out of a frustration with the Strava app.

Whilst it’s great for collecting data, it has some limitations in how it shows that data back to me. The training log shows all of my runs and bike rides, but nothing else. However, I do a lot of hiking too (especially when I’m nursing a running injury) and to me, it’s all one and the same. I want to see it all my activities on the same training log.

So that’s why I built this activity dashboard in Google Data Studio. It shows all of my activities, regardless of type, in a single view.

Connecting the Strava API with Google Sheets

To connect to the Strava API with Google Sheets, follow these steps:

Setup your Google Sheet

  1. Open a new Google Sheet (pro-tip: type sheet.new into your browser window!)
  2. Type a header row in your Google Sheet: “ID”, “Name”, “Type”, “Distance (m)” into cells A1, B1, C1 and D1 respectively
  3. Open the Script Editor (Tools > Script editor)
  4. Give the script project a name e.g. Strava Sheets Integration
  5. Create a second script file (File > New > Script file) and call it oauth.gs
  6. Add the OAuth 2.0 Apps Script library to your project (Resources > Libraries...)
  7. Enter this ID code in the “Add a library” box: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
  8. Select the most recent version of the library from the drop-down (version 34 currently — September 2019) and hit “Save”

Add the code

If you’re new to API and Apps Script, check out my API Tutorial For Beginners With Google Sheets & Apps Script.

In your oauth.gs file, add this code:

var CLIENT_ID = '';
var CLIENT_SECRET = '';

// configure the service
function getStravaService() {
  return OAuth2.createService('Strava')
    .setAuthorizationBaseUrl('https://www.strava.com/oauth/authorize')
    .setTokenUrl('https://www.strava.com/oauth/token')
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('activity:read_all');
}

// handle the callback
function authCallback(request) {
  var stravaService = getStravaService();
  var isAuthorized = stravaService.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

Also available in this GitHub oauth.js repo.

In your code.gs file, add this code:

// custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Strava App')
    .addItem('Get data', 'getStravaActivityData')
    .addToUi();
}

// Get athlete activity data
function getStravaActivityData() {
  
  // get the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');

  // call the Strava API to retrieve data
  var data = callStravaAPI();
  
  // empty array to hold activity data
  var stravaData = [];
    
  // loop over activity data and add to stravaData array for Sheet
  data.forEach(function(activity) {
    var arr = [];
    arr.push(
      activity.id,
      activity.name,
      activity.type,
      activity.distance
    );
    stravaData.push(arr);
  });
  
  // paste the values into the Sheet
  sheet.getRange(sheet.getLastRow() + 1, 1, stravaData.length, stravaData[0].length).setValues(stravaData);
}

// call the Strava API
function callStravaAPI() {
  
  // set up the service
  var service = getStravaService();
  
  if (service.hasAccess()) {
    Logger.log('App has access.');
    
    var endpoint = 'https://www.strava.com/api/v3/athlete/activities';
    var params = '?after=1546300800&per_page=200';

    var headers = {
      Authorization: 'Bearer ' + service.getAccessToken()
    };
    
    var options = {
      headers: headers,
      method : 'GET',
      muteHttpExceptions: true
    };
    
    var response = JSON.parse(UrlFetchApp.fetch(endpoint + params, options));
    
    return response;  
  }
  else {
    Logger.log("App has no access yet.");
    
    // open this url to gain authorization from github
    var authorizationUrl = service.getAuthorizationUrl();
    
    Logger.log("Open the following URL and re-run the script: %s",
        authorizationUrl);
  }
}

Also available in this GitHub code.js repo.

Note about the params variable

Have a look at the params variable:

var params = '?after=1546300800&per_page=200'

The ‘after’ parameter means my code will only return Strava activities after the date I give. The format of the date is epoch time and the date I’ve used here is 1/1/2019 i.e. I’m only returning activities from this year.

(Here’s a handy calculator to convert human dates to epoch timestamps.)

The other part of the params variable is the ‘per_page’ variable, which I’ve set to 200. This is the maximum number of records the API will return in one batch.

To get more than 200, you need to add in the ‘page’ parameter and set it to 2,3,4 etc. to get the remaining activities, e.g.

var params = '?after=1546300800&per_page=200&page=2'

Eventually you’ll want to do that programmatically with a while loop (keep looping while the API returns data and stop when it comes back empty handed).

Note about parsing the data

The script above parses the response from the API and adds 4 values to the array that goes into the Google Sheet, namely: ID, Name, Type and Distance.

You can easily add more fields however.

Look at the Strava documentation to see what fields are returned and select the ones you want. For example, you add total elevation gain like this:

activity.total_elevation_gain

If you add extra fields to the array, don’t forget to change the size of the range you’re pasting the data into in your Google Sheet.

The array and range dimensions must match.

Setup your Strava API application

You need to create your app on the Strava platform, so that your Google Sheet can connect to it.

Login to Strava and go to Settings > My API Application or type in https://www.strava.com/settings/api

This will take you to the API application settings page.

Give your application a name, and enter a website and description. You can put anything you want here, as it’s just for display.

The key to unlock the Strava API, which took me a lot of struggle to find, is to set the “Authorization Callback Domain” as

script.google.com

(Hat tip to this article from Elif T. Kuş, which was the only place I found this.)

Strava API application setup

Next, grab your client ID and paste it into the CLIENT_ID variable on line 1 of your Apps Script code in the oauth.gs file.

Similarly, grab your client secret and paste it into the CLIENT_SECRET variable on line 2 of your Apps Script code in the oauth.gs file.

Copy these two values:

Strava API application

And paste them into your code here:

Oauth client id and secret in Apps Script

Authorize your app

Run the onOpen function from the script editor and authorize the scopes the app needs (external service and spreadsheet app):

Apps Script authorization

If your process doesn’t look like this, and you see a Warning sign, then don’t worry. Click the Advanced option and authorize there instead (see how in this Google Apps Script: A Beginner’s Guide).

Return to your Google Sheet and you’ll see a new custom menu option “Strava App”.

Click on it and select the “Get data” drop-down.

Nothing will happen in your Google Sheet the first time it runs.

Return to the script editor and open the logs (View > Logs). You’ll see the authorization URL you need to copy and paste into a new tab of your browser.

This prompts you to authorize the Strava app:

Strava API with Google Sheets authorization workflow

Boom! Now you’ve authenticated your application.

For another OAuth example, have a look at the GitHub to Apps Script integration which shows these steps for another application.

Retrieve your Strava data!

Now, the pièce de résistance!

Run the “Get data” function again and this time, something beautiful will happen.

Rows and rows of Strava data will appear in your Google Sheet!

Connect Strava API with Google Sheets

The code connects to the athlete/activities endpoint to retrieve data about all your activities.

In its present setup, shown in the GIF above, the code parses the data returned by the API and pastes 4 values into your Google Sheet: ID, Name, Type and Distance.

(The distance is measure in meters.)

Of course, you can extract any or all of the fields returned by the API.

In the data studio dashboard I’ve used some of the time data to determine what day of the week and what week of the year the activity occurred on. I also looked at fields measuring how long the activity took.

Setting a trigger to call the API automatically

Once you’ve established the basic connection above, you’ll probably want to set up a trigger to call the API once a day to get fresh data.

You’ll want to filter out the old data to prevent ending up with duplicate entries. You can use a filter loop to compare the new data with the values you have in your spreadsheet and discard the ones you already have.

Building a dashboard in Google Data Studio

Google Data Studio is an amazing tool for creating visually stunning dashboards.

I was motivated to build my own training log that had all of my activities showing, regardless of type.

First, I created some calculated fields in Apps Script to work out the day of the week and the week number. I added these four fields to my code.gs file:

(new Date(activity.start_date_local)).getDay(), // sunday - saturday: 0 - 6
parseInt(Utilities.formatDate(new Date(activity.start_date_local), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "w")), // week number
(new Date(activity.start_date_local)).getMonth() + 1, // add 1 to make months 1 - 12
(new Date(activity.start_date_local)).getYear() // get year

And I converted the distance in metres into a distance in miles with this modification in my Apps Script code.gs file:

(activity.distance * 0.000621371).toFixed(2), // distance in miles

From there, you simply create a new dashboard in Data Studio and connect it to your Google Sheet.

The activity log chart is a bubble chart with day of the week on the x-axis and week number as the y-axis, both set to average (so each appears separately). The bubble size is the Distance and the dimension is set to Name.

Bubble chart in Data Studio

Next, I added a Year filter so that I can view each year separately (I’ve got data going back to 2014 in the dataset).

To complete the dashboard, I added a Strava logo and an orange theme.

Strava api with google sheets and data studio

(Note: There’s also an open source Strava API connector for Data Studio, so you could use that to create Strava visualizations and not have to write the code yourself.)

Next steps for the Strava API with Google Sheets

This whole project was conceived as a way to explore the Strava API with Google Sheets. I’m happy to get it working and share it here.

However, I’ll be the first to admit that this project is still a little rough around the edges.

But I am excited to have my Strava data in a Google Sheet now. There are TONS of other interesting stories / trends that I want to explore when I have the time.

There is definitely room for improvement with the Apps Script code. In addition to those mentioned above, and with a little more time, I would bake the OAuth connection into the UI of the Google Sheet front end (using a sidebar), instead of needing to grab the URL from the Logs in your script editor.

And the Data Studio dashboard was rather hastily thrown together to solve the issue of not seeing all my activity data in once place. Again, there’s a lot more work you could do here to improve it.

To be continued…

Time for a run first though!

Add A Google Sheets Button To Run Scripts

Learn how to add a Google Sheets button to run your Google Apps Script functions.

Let’s see how this works with a simple example.

Imagine you have an invoice template you use on a regular basis, but it’s a pain to clear out all the values each time you need to start over. Well, you can add a button to Google Sheets so you can run scripts and clear your invoice with a single button click.

Google Sheets button

Let’s start by creating a basic invoice template with placeholders to hold information:

Add button to Google Sheets invoice example

The user can enter information into cells B5, B8, E5 and E6 (shown in yellow).

In the script editor, accessed through Tools > Script Editor, add a very simple script to clear these specific cells out:

function clearInvoice() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var invoiceNumber = sheet.getRange("B5").clearContent();
  var invoiceAmount = sheet.getRange("B8").clearContent();
  var invoiceTo = sheet.getRange("E5").clearContent();
  var invoiceFrom = sheet.getRange("E6").clearContent(); 
}

You can run this function from the script editor and it will clear out the contents of the invoice.

But that’s a pain.

You don’t want to have to open up the script editor every time. You want to do that directly from your Google Sheet.

To do that, add a Google Sheets button.

You add a button via the Insert > Drawing menu.

This brings up the drawing editor where you can easily add a box and style it to look like a button:

Google Sheets button drawing

When you click Save and Close, this drawing gets added to your Google Sheet. You can click on it to resize it or drag it around to reposition it.

To assign a script, click the three little dots in the top right of the drawing and select Assign Script:

Google Sheets button assign script

Then type in the name of the function you want to run from your Apps Script code. In this example, choose the clearInvoice function (i.e. like the code above!).

Now, when you click the button it will clear out the invoice for you!

Button with apps script in google sheets

Note: to edit or move the button after you’ve assigned it to a script, you now need to right-click on it.

See Create, insert & edit drawings in the Google Documentation for more info on the Drawing feature.

Google Apps Script: A Beginner’s Guide

What is Google Apps Script?

Google Apps Script is a cloud based scripting language for extending the functionality of Google Apps and building lightweight cloud-based applications.

What does this mean in practice?

It means you use Apps Script to write small programs that extend the standard features of Google Apps. I like to say it’s great for filling in the gaps in your workflows.

For example, I used to be overwhelmed with feedback from my courses and couldn’t respond to everyone. Now, when a student submits their feedback, my script creates a draft email in Gmail ready for me to review. It includes all the feedback so I can read it within Gmail and respond immediately.

It made a previously impossible task manageable.

With Apps Script, you can do cool stuff like automating repetitive tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services you use.

Writing your first Google Script

In this Google Sheets script tutorial, we’re going to write a script that is bound to our Google Sheet. This is called a container-bound script.

(If you’re looking for more advanced examples and tutorials, check out the full list of Apps Script articles on my homepage.)

Hello World in Google Apps Script

Let’s write our first, extremely basic program, the classic “Hello world” program beloved of computer teaching departments the world over.

Begin by creating a new Google Sheet.

Then click the menu Tools > Script editor... to open a new tab with the code editor window.

This will open a new tab in your browser, which is the Google Apps Script editor window:

Google Apps Script editor window

By default, it’ll open with a single Google Script file (code.gs) and a default code block, myFunction():

function myFunction() {
  
}

In the code window, between the curly braces after the function myFunction() syntax, write the following line of code so you have this in your code window:

function myFunction() {
  Browser.msgBox("Hello World!");
}

Your code window should now look like this:

Google Sheets script tutorial editor menu

Google Apps Script Authorization

Google Scripts have robust security protections to reduce risk from unverified apps, so we go through the authorization workflow when we first authorize our own apps.

When you hit the run button (the black triangle) for the first time, you will be prompted to authorize the app to run:

Google Apps Script Authorization

Clicking Continue pops up another window in turn, showing what permissions your app needs to run. In this instance the app wants to view and manage your spreadsheets in Google Drive, so click Allow (otherwise your script won’t be able to interact with your spreadsheet or do anything):

Google Apps Script Authorization

❗️When your first run your apps script, you may see the “app isn’t verified” screen and warnings about whether you want to continue.

In our case, since we are the creator of the app, we know it’s safe so we do want to continue. Furthermore, the apps script projects in this post are not intended to be published publicly for other users, so we don’t need to submit it to Google for review (although if you want to do that, here’s more information).

Click the “Advanced” button in the bottom left of the review permissions pop-up, and then click the “Go to Starter Script Code (unsafe)” at the bottom of the next screen to continue. Then type in the words “Continue” on the next screen, click Next, and finally review the permissions and click “ALLOW”, as shown in this image (showing different script):

More information can be found in this detailed blog post from Google Developer Expert Martin Hawksey.

Running a function in Apps Script

Once you’ve authorized a Google App script, the function will run (or execute). You will see two status messages to tell you what’s happening.

First this one:

GAS execute script

And then this one:

GAS execute status 2

If anything goes wrong with your code, this is stage when you’d see a warning message (instead of the yellow message, you’ll get a red box with an error message in it).

Now, assuming you got those two yellow status messages and they’ve both automatically disappeared from view, then your program has run successfully. Click back on the browser tab with your spreadsheet (most likely the tab to the left of the one we’re in).

You should see the output of your program, a message box popup with the classic “Hello world!” message:

Google Apps Script output hello world

Click on Ok to dismiss.

Great job! You’ve now written your first apps script program.

Rename functions in Google Apps Script

We should rename our function to something more meaningful.

At present, it’s called myFunction which is the default, generic name generated by Google. Every time I want to call this function (i.e. run it to do something) I would write myFunction(). This isn’t very descriptive, so let’s rename it to helloWorld(), which gives us some context.

So change your code in line 1 from this:

function myFunction() {
  Browser.msgBox("Hello World!");
}

to this:

function helloWorld() {
  Browser.msgBox("Hello World!");
}

Note, it’s convention in Apps Script to use the CamelCase naming convention, starting with a lowercase letter. Hence, we name our function helloWorld, with a lowercase h at the start of hello and an uppercase W at the start of World.

Adding a custom menu in Google Apps Script

In its current form, our program is pretty useless for many reasons, not least because we can only run it from the script editor window and not from our spreadsheet.

Let’s fix that by adding a custom menu to the menu bar of our spreadsheet, so that a user can run the script within the spreadsheet without needing to open up the editor window.

This is actually surprisingly easy to do, requiring only a few lines of code. Add the following 6 lines of code into the editor window, above the helloWorld() function we created above, as shown here:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My Custom Menu')
      .addItem('Say Hello', 'helloWorld')
      .addToUi();
}

function helloWorld() {
  Browser.msgBox("Hello World!");
}

If you look back at your spreadsheet tab in the browser now, nothing will have changed. You won’t have the custom menu there yet. We need to re-open our spreadsheet (refresh it) or run our onOpen() script first, for the menu to show up.

To run onOpen() from the editor window, first select the onOpen function as shown in this image:

Google Apps Script custom menu

Once you’ve selected the onOpen function, the small triangle button will change from light gray to black, meaning it can be clicked to run your chosen function:

Run function

Now, when you return to your spreadsheet you’ll see a new menu on the right side of the Help option, called My Custom Menu. Click on it and it’ll open up to show a choice to run your Hello World program:

Custom menu

Run functions from buttons in Google Sheets

An alternative way to run Google Scripts from your Sheets is to bind the function to a button in your Sheet.

For example, here’s an invoice template Sheet with a RESET button to clear out the contents:

Button with apps script in google sheets

For more information on how to do this, have a look at this post: Add A Google Sheets Button To Run Scripts

Google Apps Script Examples

Macros in Google Sheets

Another great way to get started with Google Scripts is by using Macros. Macros are small programs in your Google Sheets that you record so that you can re-use them (for example applying a standard formatting to a table). They use Apps Script under the hood so are a great way to get started in seeing what you can do.

Read more: The Complete Guide to Simple Automation using Google Sheets Macros

Custom function using Google Apps Script

Let’s create a custom function with Apps Script, and also demonstrate the use of the Maps Service. We’ll be creating a small custom function that calculates the driving distance between two points, based on Google Maps Service driving estimates.

The goal is to be able to have two place-names in our spreadsheet, and type the new function in a new cell to get the distance, as follows:

GAS custom function for maps

The solution should be:

GAS custom map function output

Copy the following code into the Apps Script editor window and save. First time, you’ll need to run the script once from the editor window and click “Allow” to ensure the script can interact with your spreadsheet.

function distanceBetweenPoints(start_point, end_point) {
  // get the directions
  var directions = Maps.newDirectionFinder()
     .setOrigin(start_point)
     .setDestination(end_point)
     .setMode(Maps.DirectionFinder.Mode.DRIVING)
     .getDirections();
  
  // get the first route and return the distance
  var route = directions.routes[0];
  var distance = route.legs[0].distance.text;
  return distance;
}

Saving data with Google Apps Script

Let’s take a look at another simple use case for this Google Sheets Apps Script tutorial.

Here, I’ve setup an importxml function to extract the number of followers a specific social media channel has (e.g. in this case a Reddit channel), and I want to save copy of that number at periodic intervals, like so:

save data in google sheet

In this script, I’ve created a custom menu (as we did above) to run my main function. The main function, saveData(), copies the top row of my spreadsheet (the live data) and pastes it to the next blank line below my current data range as text, thereby “saving” a snapshot in time.

The code for this example is:

// custom menu function
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Save Data','saveData')
      .addToUi();
}

// function to save data
function saveData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var url = sheet.getRange('Sheet1!A1').getValue();
  var follower_count = sheet.getRange('Sheet1!B1').getValue();
  var date = sheet.getRange('Sheet1!C1').getValue();
  sheet.appendRow([url,follower_count,date]);
}

See this post: Saving Data in Google Sheets, for a step-by-step guide to creating and running this script.

Google Apps Script example in Google Docs

Google Apps Script is by no means confined to Sheets only, and is equally applicable in the Google Docs environment. Here’s a quick example of a script that inserts a specific symbol or text string into your Doc wherever your cursor is:

Google Docs Apps Script

We do this using Google App Scripts as follows:

1. Create a new Google Doc

2. Open script editor from the menu: Tools > Script editor...

3. Click on: Create script for > Blank Project

Google Apps script menu

4. In the newly opened Script tab, remove all of the boilerplate code (the `myFunction` code block)

5. Copy in the following code:

// code to add the custom menu
function onOpen() {
  var ui = DocumentApp.getUi();
  ui.createMenu('My Custom Menu')
      .addItem('Insert Symbol', 'insertSymbol')
      .addToUi();
};

// code to insert the symbol
function insertSymbol() {  
  // add symbol at the cursor position
  var cursor = DocumentApp.getActiveDocument().getCursor();
  var element = cursor.insertText('§§');
  
};

6. You can change the special character in this line

var element = cursor.insertText('§§');

to whatever you want it to be, e.g.

var element = cursor.insertText('( ͡° ͜ʖ ͡°)');

7. Click Save and give your script project a name (doesn’t affect the running so call it what you want e.g. Insert Symbol)

8. Run the script for the first time by clicking on the menu: Run > onOpen

9. Google will recognize the script is not yet authorized and ask you if you want to continue. Click Continue

10. Since this the first run of the script, Google Docs asks you to authorize the script (I called my script “test” which you can see below):

Docs Apps Script Auth

11. Click Allow

12. Return to your Google Doc now.

13. You’ll have a new menu option, so click on it:
My Custom Menu > Insert Symbol

14. Click on Insert Symbol and you should see the symbol inserted wherever your cursor is.

Google Apps Script Tip: Use the Logger class

Use the Logger class to output text messages to the log files, to help debug code.

The log files can be accessed after the program has finished running, by going to View > Show Logs (or Cmd + Enter, or Ctrl + Enter (on PC)).

The syntax in its most basic form is Logger.log(something in here). This records the value(s) of variable(s) at different steps of your program.

For example, add this script to a code file your editor window:

function logTimeRightNow() {
  var timestamp = new Date();
  Logger.log(timestamp);
}

Run the script in the editor window, then View > Show Logs and you should see:

logger output

Real world examples from my own work

I’ve only scratched the surface of what’s possible using G.A.S. to extend the Google Apps experience.

Here’s a couple of interesting projects I’ve worked on:

1) A Sheets/web-app consisting of a custom web form that feeds data into a Google Sheet (including uploading images to Drive and showing thumbnails in the spreadsheet), then creates a PDF copy of the data in the spreadsheet and automatically emails it to the users. And with all the data in a master Google Sheet, it’s possible to perform data analysis, build dashboards showing data in real-time and share/collaborate with other users.

2) A dashboard that connects to a Google Analytics account, pulls in social media data, checks the website status and emails a summary screenshot as a PDF at the end of each day.

Marketing dashboard using Google Apps Script

3) A marking template that can send scores/feedback to students via email and Slack, with a single click from within Google Sheets. Read more in this article: Save time with this custom Google Sheets, Slack & Email integration

Send data from Google Sheets to Slack

My own journey into Google Apps Script

My friend Julian, from Measure School, interviewed me in May 2017 about my journey into Apps Script and my thoughts on getting started:

Google Apps Script Resources

For further reading, I’ve created this list of resources for information and inspiration:

Course

Documentation

Official Google Documentation

G Suite Developers Blog

Communities

Google Apps Script Group

Stack Overflow GAS questions

Books

Going GAS book

Going GAS by Bruce Mcpherson is a newly published (i.e. bang up-to-date as of April 2016) book covering the entire GAS ecosystem, with a specific focus on making the transition from Office/VBA into Google Apps/GAS. Even if you don’t use Office or VBA much or at all, it’s still a very useful resource. It’s been a few years since I’ve done any serious VBA work, but I still found the book very helpful and a great overview of the GAS environment.


Imagination and patience to learn are the only limits to what you can do and where you can go with GAS. I hope you feel inspired to try extending your Sheets and Docs and automate those boring, repetitive tasks!

Related Articles

API Tutorial For Beginners With Google Sheets & Apps Script

In this API tutorial for beginners, you’ll learn how to connect to APIs using Google Apps Script, to retrieve data from a third-party and display it in your Google Sheet.

Example 1 shows you how to use Google Apps Script to connect to a simple API to retrieve some data and show it in Google Sheets:

API tutorial for beginners: Random math facts from Numbers API in Google Sheet

In Example 2, we’ll use Google Apps Script to build a music discovery application using the iTunes API:

Itunes API with Google Sheets

Finally, in example 3, I’ll leave you to have a go at building a Star Wars data explorer application, with a few hints:

Star Wars API explorer in Google Sheets using Google Apps Script

API tutorial for beginners: what is an API?

You’ve probably heard the term API before. Maybe you’ve heard how tech companies use them when they pipe data between their applications. Or how companies build complex systems from many smaller micro-services linked by APIs, rather than as single, monolithic programs nowadays.

API stands for “Application Program Interface”, and the term commonly refers to web URLs that can be used to access raw data. Basically, the API is an interface that provides raw data for the public to use (although many require some form of authentication).

As third-party software developers, we can access an organization’s API and use their data within our own applications.

The good news is that there are plenty of simple APIs out there, which we can cut our teeth on. We’ll see three of them in this beginner api tutorial.

We can connect a Google Sheet to an API and bring data back from that API (e.g. iTunes) into our Google Sheet using Google Apps Script. It’s fun and really satisfying if you’re new to this world.

API tutorial for beginners: what is Apps Script?

In this API tutorial for beginners, we’ll use Google Apps Script to connect to external APIs.

Google Apps Script is a Javascript-based scripting language hosted and run on Google servers, that extends the functionality of Google Apps.

If you’ve never used it before, check out my post: Google Apps Script: A Beginner’s Guide

Example 1: Connecting Google Sheets to the Numbers API

We’re going to start with something super simple in this beginner api tutorial, so you can focus on the data and not get lost in lines and lines of code.

Let’s write a short program that calls the Numbers API and requests a basic math fact.

Step 1: Open a new Sheet

Open a new blank Google Sheet and rename it: Numbers API Example

Step 2: Go to the Apps Script editor

Navigate to Tools > Script Editor...

Access script editor through toolbar

Step 3: Name your project

A new tab opens and this is where we’ll write our code. Name the project: Numbers API Example

Step 4: Add API example code

Remove all the code that is currently in the Code.gs file, and replace it with this:

function callNumbers() {
  
  // Call the Numbers API for random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());
  
}

We’re using the UrlFetchApp class to communicate with other applications on the internet to access resources, to fetch a URL.

Now your code window should look like this:

Numbers API Google Apps Script code

Step 5: Run your function

Run the function by clicking the play button in the toolbar:

Run Apps Script button

Step 6: Authorize your script

This will prompt you to authorize your script to connect to an external service. Click “Review Permissions” and then “Allow” to continue.

Apps Script Review Permissions

Apps Script authorization

Step 7: View the logs

Congratulations, your program has now run. It’s sent a request to a third-party for some data (in this case a random math fact) and that service has responded with that data.

But wait, where is it? How do we see that data?

Well you’ll notice line 5 of our code above was Logger.log(....) which means that we’ve recorded the response text in our log files.

So let’s check it out.

Go to View > Logs:

View apps script logs

You’ll see your answer (you may of course have a different fact):

[17-02-03 08:52:41:236 PST] 1158 is the maximum number of pieces a torus can be cut into with 18 cuts.

which looks like this in the popup window:

Apps script logger output

Great! Try running it a few times, check the logs and you’ll see different facts.

Next, try changing the URL to these examples to see some different data in the response:

http://numbersapi.com/random/trivia
http://numbersapi.com/4/17/date
http://numbersapi.com/1729

You can also drop these directly into your browser if you want to play around with them. More info at the Numbers API page.

So, what if we want to print the result to our spreadsheet?

Well, that’s pretty easy.

Step 8: Add data to Sheet

Add these few lines of code (lines 7, 8 and 9) underneath your existing code:

function callNumbers() {
  
  // Call the Numbers API for random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());
  
  var fact = response.getContentText();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1,1).setValue([fact]);
  
}

Line 7 simply assigns the response text (our data) to a variable called fact, so we can refer to it using that name.

Line 8 gets hold of our current active sheet (Sheet1 of Numbers API Example spreadsheet) and assigns it to a variable called sheet, so that we can access it using that name.

Finally in line 9, we get cell A1 (range at 1,1) and set the value in that cell to equal the variable fact, which holds the response text.

Step 9: Run & re-authorize

Run your program again. You’ll be prompted to allow your script to view and manage your spreadsheets in Google Drive, so click Allow:

Apps Script Review Permissions

Apps script spreadsheet authorization

Step 10: See external data in your Sheet

You should now get the random fact showing up in your Google Sheet:

Random math fact from Numbers API in Google Sheet

How cool is that!

To recap our progress so far in this API Tutorial for Beginners: We’ve requested data from a third-party service on the internet. That service has replied with the data we wanted and now we’ve output that into our Google Sheet!

Step 11: Copy data into new cell

The script as it’s written will always overwrite cell A1 with your new fact every time you run the program. If you want to create a list and keep adding new facts under existing ones, then make this minor change to line 9 of your code (shown below), to write the answer into the first blank row:

function callNumbers() {
  
  // Call the Numbers API for random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());
  
  var fact = response.getContentText();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1,1).setValue([fact]);
  
}

Your output now will look like this:

Random math facts from Numbers API in Google Sheet

One last thing we might want to do with this application is add a menu to our Google Sheet, so we can run the script from there rather than the script editor window. It’s nice and easy!

Step 12: Add the code for a custom menu

Add the following code in your script editor:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Numbers API Menu')
      .addItem('Display random number fact','callNumbers')
      .addToUi();
}

Your final code for the Numbers API script should now match this code on GitHub.

Step 13: Add the custom menu

Run the onOpen function, which will add the menu to the spreadsheet. We only need to do this step once.

Add custom apps script menu

Step 14: Run your script from the custom menu

Use the new menu to run your script from the Google Sheet and watch random facts pop-up in your Google Sheet!

Use custom apps script menu

Alright, ready to try something a little harder?

Let’s build ourselves a music discovery application in Google Sheets.

Example 2: Music Discovery Application using the iTunes API

This application retrieves the name of an artist from the Google Sheet, sends a request to the iTunes API to retrieve information about that artist and return it. It then displays the albums, song titles, artwork and even adds a link to sample that track:

Google Sheets and iTunes API using Apps Script

It’s actually not as difficult as it looks.

Getting started with the iTunes API Explorer

Start with a blank Google Sheet, name it “iTunes API Explorer” and open up the Google Apps Script editor.

Clear out the existing Google Apps Script code and paste in this code to start with:

function calliTunes() {
  
  // Call the iTunes API
  var response = UrlFetchApp.fetch("https://itunes.apple.com/search?term=coldplay");
  Logger.log(response.getContentText());
}

Run the program and accept the required permissions. You’ll get an output like this:

iTunes API output

Woah, there’s a lot more data being returned this time so we’re going to need to sift through it to extract the bits we want.

Parsing the iTunes data

So try this. Update your code to parse the data and pull out certain bits of information:

function calliTunes() {
  
  // Call the iTunes API
  var response = UrlFetchApp.fetch("https://itunes.apple.com/search?term=coldplay");
  
  // Parse the JSON reply
  var json = response.getContentText();
  var data = JSON.parse(json);
  
  Logger.log(data);
  Logger.log(data["results"]);
  Logger.log(data["results"][0]);
  Logger.log(data["results"][0]["artistName"]);
  Logger.log(data["results"][0]["collectionName"]);
  Logger.log(data["results"][0]["artworkUrl60"]);
  Logger.log(data["results"][0]["previewUrl"]);
  
}

Line 4: We send a request to the iTunes API to search for Coldplay data. The API responds with that data and we assign it to a variable called response, so we can use that name to refer to it.

Lines 7 and 8: We get the context text out of the response data and then parse the JSON string response to get the native object representation. This allows us to extract out different bits of the data.

So, looking first at the data object (line 10):

iTunes api data packet

You can see it’s an object with the curly brace at the start {

The structure is like this:

{
resultCount = 50,
results = [ ....the data we're after... ]
}

iTunes api data packet

Line 11: we extract the “results”, which is the piece of data that contains the artist and song information, using:

data["results"]

Line 12: There are multiple albums returned for this artist, so we grab the first one using the [0] reference since the index starts from 0:

data["results"][0]

This shows all of the information available from the iTunes API for this particular artist and album:

iTunes api data results

Lines 13 – 16: Within this piece of data, we can extract specific details we want by referring to their names:

e.g. data["results"][0]["collectionName"]

to give the following output:

iTunes api details

Use comments (“//” at the start of a line) to stop the Logger from logging the full data objects if you want. i.e. change lines 10, 11 and 12 to be:

// Logger.log(data);
// Logger.log(data[“results”]);
// Logger.log(data[“results”][0]);

This will make it easier to see the details you’re extracting.

Putting this altogether in an application

If we want to build the application that’s showing in the GIF at the top of this post, then there are a few steps we need to go through:

  • Setup the Google Sheet
  • Retrieve the artist name from the Google Sheet with Google Apps Script
  • Request data from iTunes for this artist with Google Apps Script
  • Parse the response to extract the relevant data object with Google Apps Script
  • Extract the specific details we want (album name, song title, album artwork, preview url)
  • Clear out any previous results in the Google Sheet before showing the new results
  • Display the new results in our Google Sheet
  • Add a custom menu to run the program from the Google Sheet, not the script editor

It’s always a good idea to write out a plan like this before you commit to writing any lines of code.

That way you can think through the whole application and what it’s going to do, which allows you to make efficient choices with how you setup your code.

So the first thing to do is setup a Google Sheet. I’ll leave this up to you, but here’s a screenshot of my basic Google Sheet setup:

iTunes Google Sheet

The important thing to note is the location of the cell where a user types in the artist name (11th row, 2nd column) as we’ll be referring to that in our code.

iTunes API Explorer code

And here’s the Google Apps Script code for our application:

// --------------------------------------------------------------------------------------------------
//
// iTunes Music Discovery Application in Google Sheets
//
// --------------------------------------------------------------------------------------------------

// custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom iTunes Menu')
      .addItem('Get Artist Data','displayArtistData')
      .addToUi();
}

// function to call iTunes API
function calliTunesAPI(artist) {
  
  // Call the iTunes API
  var response = UrlFetchApp.fetch("https://itunes.apple.com/search?term=" + artist + "&limit=200");
  
  // Parse the JSON reply
  var json = response.getContentText();
  return JSON.parse(json);
  
}


function displayArtistData() {
  
  // pick up the search term from the Google Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  var artist = sheet.getRange(11,2).getValue();
  
  var tracks = calliTunesAPI(artist);
  
  var results = tracks["results"];
  
  var output = []
  
  results.forEach(function(elem,i) {
    var image = '=image("' + elem["artworkUrl60"] + '",4,60,60)';
    var hyperlink = '=hyperlink("' + elem["previewUrl"] + '","Listen to preview")';
    output.push([elem["artistName"],elem["collectionName"],elem["trackName"],image,hyperlink]);
    sheet.setRowHeight(i+15,65);
  });
  
  // sort by album
  var sortedOutput = output.sort( function(a,b) {
    
    var albumA = (a[1]) ? a[1] : 'Not known';  // in case album name undefined 
    var albumB = (b[1]) ? b[1] : 'Not known';  // in case album name undefined
    
    if (albumA < albumB) { return -1; } else if (albumA > albumB) {
      return 1;
    }
    // names are equal
    return 0;
  });
  
  // adds an index number to the array
  sortedOutput.forEach(function(elem,i) {
    elem.unshift(i + 1);
  });
  
  var len = sortedOutput.length;
  
  // clear any previous content
  sheet.getRange(15,1,500,6).clearContent();
  
  // paste in the values
  sheet.getRange(15,1,len,6).setValues(sortedOutput);
  
  // formatting
  sheet.getRange(15,1,500,6).setVerticalAlignment("middle");
  sheet.getRange(15,5,500,1).setHorizontalAlignment("center");
  sheet.getRange(15,2,len,3).setWrap(true);
  
}

Here’s the iTunes API script file on GitHub.

How it works:

Let’s talk about a few of the key lines of code in this program:

Lines 16 – 25 describe a function that takes an artist name, calls the API with this artist name and then returns the search results from the API. I’ve encapsulated this as a separate function so I can potentially re-use it elsewhere in my program.

The main program starts on line 28.

On line 34, I retrieve the name of the artist that has been entered on the Google Sheet, and we call our API function with this name on line 36.

On lines 42 – 47, I take the results returned by the API, loop over them and pull out just the details I want (artist name, album name, song title, album artwork and preview track). I push all of this into a new array called output.

Next I sort and add an index to the array, although both of these are not mandatory steps.

On line 68, I clear out any previous content in my sheet.

Then on line 71, I paste in the new data, starting at row 15.

Finally, lines 74 – 76 format the newly pasted data, so that the images have space to show properly.

Run the onOpen() function from the script editor once to add the custom menu to your Google Sheet. Then you’ll be able to run your iTunes code from the Google Sheet:

Custom iTunes API menu

Now you can run the program to search for your favorite artist!

More details on the iTunes API:

Documentation for searching the iTunes Store.

Documentation showing the search results JSON packet.

Example 3: Star Wars data explorer using the Star Wars API

This one is a lot of fun! Definitely the most fun example in this API Tutorial for Beginners.

The Star Wars API is a database of all the films, people, planets, starships, species and vehicles in the Star Wars films. It’s super easy to query and the returned data is very friendly.

Star Wars API in Google Sheet

It’s a little easier than the iTunes API because the data returned is smaller and more manageable, and therefore easier to parse when you first get hold of it.

Getting started with the Star Wars API

As with both the previous APIs, start with a simple call to see what the API returns:

/*
 * Step 1:
 * Most basic call to the API 
 */
function swapi() {
  
  // Call the Star Wars API
  var response = UrlFetchApp.fetch("http://swapi.co/api/planets/1/");
  Logger.log(response.getContentText());
}

The data returned looks like this:

API Tutorial for Beginners: Star Wars API data

So, it’s relatively easy to get the different pieces of data you want, with code like this:

/*
 * Step 2:
 * Same basic call to the API 
 * Parse the JSON reply
 */
function swapi() {
  
  // Call the Star Wars API
  var response = UrlFetchApp.fetch("http://swapi.co/api/planets/1/");
  
  // Parse the JSON reply
  var json = response.getContentText();
  var data = JSON.parse(json);
  Logger.log(data);
  Logger.log(data.name);
  Logger.log(data.population);
  Logger.log(data.terrain);
}

Well, that should be enough of a hint for you to give this one a go!

Some other tips

In addition to custom menus to run scripts from your Google Sheet, you can add buttons to your Google Sheet and connect them to a script to run the script when they are clicked. That’s what I’ve done in this example.

On the menu, Insert > Drawing...

Google Sheets insert drawing

Create a button using the rectangle tool:

Google Sheet drawing tool

Finally, right click the drawing when it’s showing in your sheet, and choose Assign Script and type in the name of the function you want to run:

Google Sheet drawing assign script

What else?

Use this formula to add stars to your Google Sheet:

=char(9734)

I used the font “Orbitron” across the whole worksheet and, whilst it’s not a Star Wars font, it still has that space-feel to it.

The big Star Wars logo is simply created by merging a bunch of cells and using the IMAGE() formula with a suitable image from the web.

Finally, here’s my SWAPI script on GitHub if you want to use it.

API Tutorial for Beginners: Other APIs to try

Here are a few other beginner-friendly APIs to experiment with:

> Giphy API. Example search endpoint: Funny cat GIFs

> Pokémon API. Example search endpoint: Pokemon no. 1

> Open Movie Database API. Example search endpoint: Batman movies

> International Space Station Current Location. Example search endpoint: ISS current location

Also, here’s the official Google documentation on connecting to external APIs.

Let me know in the comments what you do with all these different APIs!