How to Call Google APIs from Apps Script

,

The original goal in writing this post was to passive-aggressively discuss Apps Script (AS, for our purposes) and how it executes Google API calls in the hope that expressing myself in such a way would help to relieve some of the tension developed in figure this out.

I’ve changed my mind. Let’s just go through the steps and hopefully I can help you avoid passive-aggressive behavior that is best saved for occasions like Thanksgiving or other family holidays.

Today’s goal: write an example Apps Script that executes calls out to a Google API.

Why is today’s goal interesting? Because understanding why calling APIs don’t work out of the box is just as important as being told how to make it work.

Let’s look at the steps to failure (apologies for not using images).

The Steps

  • Create an Apps Script project
    • Go to https://script.google.com/home. If that link doesn’t take you straight to the AS dashboard it is because you are not yet logged into your Google profile. Log in and try again. When it takes you to the AS home page just click on My Projects in the menu to the left.
    • Click on the New Project button to create a new AS project.
  • Name the project
    • Click once on the projects default title (Untitled project) and a Rename Project dialog will open. Give your project a name. I am calling mine GCP API Test. Press Rename.
  • Go to Settings -> Enable Chrome V8 Runtime (in case it is not enabled)
    • This setting should already be enabled, but in case it is not, check the box.
  • Go to Settings -> Show ‘appsscript.json’ manifest file in editor and select the checkbox
    • This setting will not be enabled. Check the box to enable it.
  • Return to the Editor
    • Go to the AS menu to the left and select the “<>” icon which will open the editor. Once it opens you should see only two files listed: appsscript.json and Code.gs.
  • Copy and paste the following into appsscript.json
Copy
{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/webmasters"
  ],
  "runtimeVersion": "V8"
}
  • Copy and paste the following into Code.gs
Copy
/**
 * Use the Search Console API to list the URLs of all the sites you have setup.
 * @see {@link https://developers.google.com/webmaster-tools/}
 * This code was taken from https://github.com/EnmanuelParache/apps-script-oauth2/tree/8c605ba364012b272b688729eab6a6fd08adbe0f/samples/NoLibrary. Many thanks!
 */
function listSearchConsoleSites() {
  var url = 'https://www.googleapis.com/webmasters/v3/sites';
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url, {
    headers: {
      Authorization: 'Bearer ' + token
    },
    muteHttpExceptions: true
  });
  var contentText = response.getContentText();
  Logger.log("contentText: " + contentText);
  var result = JSON.parse(contentText);
  Logger.log("result: " + result);
  Logger.log("result.siteEntry: " + result.siteEntry);
}
  • Save the AS project
    • Either press the disk icon or press Ctrl-S
  • Run
    • Select the Code.gs file.
    • Press Run
  • Approve everything in the various dialog boxes
    • Authorization Required -> Review Permissions
    • Sign in with Google -> Choose an Account to continue with GCP API Test
      • Select the profile you used earlier when you created the AS project
    • Google Hasn’t Verified This App -> Advanced -> Go to GCP API Test (unsafe)
    • GCP API Test Wants to Access Your Google Account -> Allow
  • FAIL
    • The Execution Log panel should open and mention that Execution Started and print out a somewhat long error. The important things:
      • Status: PERMISSION_DENIED
      • Message: Google Search Console API has not been used in project 986672787818 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/searchconsole.googleapis.com/overview?project=986672787818 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.

So what just happened? By rights all of the above should have worked. Here is the problem: every AS project automatically gets a Google Cloud Platform (GCP) project associated with it. Any APIs you want to use need to be cleared through the user (in this case, you), but the default project has not been configured to allow you to use the above API (or any API for that matter, AFAIK). The error message seems cryptic because it references a project you didn’t create, with a project ID you’ve never seen, and this default project is not available to be configured by you anyway. That makes the default project useful if you are not doing anything related to GCP services, and not so useful if you are.

Now do the following to make all of your above work…work.

  • Create a GCP Project
    • Log into your GCP dashboard and create a project. Can you reuse an existing project? Sure! But don’t. Always keep your work properly segregated from each other.
    • Not sure how to create a GCP project? Read this.
    • What? You don’t have a GCP account and access to free projects? Read this.
    • What? You don’t have a Google account that you can use to log into GCP? Read this.
  • From you Cloud Project Dashboard go to APIs & Services -> OAuth consent screen
    • User Type -> External -> Create
    • Enter the following in App Information
      • App name [for example, GCP API Test]
      • User Support Email [select from the dropdown. It will be the email you signed in to your project with]
    • Enter the following in Developer Contact Information
      • Email Addresses [your Google account email]
    • Press Save and Continue
    • Scopes -> Save and Continue [nothing to do on this page]
    • Test Users -> Add Users [add the email address you used when you logged into the Apps Script dashboard]
    • Press Save and Continue
  • From the GCP Menu -> APIs & Services -> Enabled APIs & Services
    • Press Enable APIs and Services
    • Search: Search Console API [you should get only one result]
    • Press Google Search Console API -> Enable
  • Return to the project dashboard (Main Menu -> Cloud Overview -> Dashboard). Copy the project number
  • Return to the Apps Script project
    • Go to Project Settings -> Google Cloud Platform (GCP) Project -> Change Project
    • Enter the project number number and press Set Project
  • Run
    • Return to the AS Editor
    • Select the Code.gs file (again).
    • Press Run (again)
  • Approve the myriad of permissions again
  • PASS [if you have any sites on GCP they will be listed. For the rest of us the following message will appear: result.siteEntry: undefined]

Sigh.

So! Every time you want to add another API call (in the code the endpoint being called was https://www.googleapis.com/webmasters/v3/sites) you would have to determine which API to enable, then enable it, test your code, and then have a very satisfying meal after seeing it work.

Preemptive FAQ

Q: I’ve seen this error come up:

Google Search Console API has not been used in project 986672787818 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/searchconsole.googleapis.com/overview?project=986672787818 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.

It makes no sense to me as I have not created a GCP project and even if I did that project number doesn’t match. What gives?

A: What happened is this: every AS project automatically gets a Google Cloud Platform (GCP) project associated with it. Any APIs you want to use need to be cleared through the user (in this case, you), but the default project has not been configured to allow you to use the above API (or any API for that matter, AFAIK). The error message seems cryptic because it references a project you didn’t create, with a project ID you’ve never seen, and a project you can’t configure anyway. What you need to do is:

  • Create a GCP project
  • Create an OAuth page following the instructions here (or cheat and just follow the steps above)
  • Enable the API you want to use in your AS project
  • Copy the project number
  • Paste the project number into the Apps Script Project Settings page

When you run your project you’ll still have to answer all the authorization questions from the various dialog boxes that open, but now you can add and subtract API endpoints with impunity (assuming you update the appsscript.json file and your Code.gs file and any other files that might use a Google API endpoint. Simple, right?

Q: How do you know which security URL to use when you want to make a call to an API endpoint?

(Sigh. Sometimes I hate smart questions) There are actually 2 parts to an API call: the endpoint that will return a response, and the security path that you need to declare in the appsscript.json file which defines which API has to be enabled for the magic to happen. The easiest way to find the security scope URL is to:

  • go to the API Explorer page
  • enter the name of the API you are looking for and click on the name
  • search for the API endpoint
  • click on the method name which will open an API Explorer sidebar
  • Credentials -> Google OAuth 2.0 -> Show Scopes

The OAuth scope URLs will be listed at that point. Sometimes you don’t need to use them all, but there will a message letting you know (otherwise, use them all).

Sadly, trying to do that for the Search Console API will give you rather odd results for some reason. The other APIs responded just fine. I’m sure everything is just peachy.

References

Every Apps Script project uses Google Cloud to manage authorization, advanced services, and other details. To configure and manage these settings, every Apps Script project has an associated Google Cloud project.

Google Cloud Project

Configure the OAuth consent screen

Thanks for reading! Hope this was useful!