0

I am trying to execute Apps Script function using Apps Script API. For this I set up a target script and calling script (JavaScript code) using instructions from Google here.

I followed exactly how it is described but I am getting the following errors.

  1. Error in calling script:

    ReferenceError: gapi is not defined

  2. Error on target script while manually running the function "getFoldersUnderRoot()"

    Exception: We're sorry, a server error occurred. Please wait a bit and try again.

function "getFoldersUnderRoot()" was running properly before connecting the target script to the GCP project.

Any help is appreciated to point out what I am doing wrong.

Partha S. Pal
  • 169
  • 1
  • 2
  • 14
  • 1
    Please provide [mcve] – Cooper Dec 27 '20 at 01:45
  • This are two unrelated questions. In the future, it would be the best if you open different questions for them. Let's start with the second one because without it, the first one won't work anyways. Make a new script with a single function with only `Logger.log('Hello, world!')` in it. Assign it to your Google Cloud project. Can you run it manually form Google Apps Script? – Martí Dec 28 '20 at 09:55

1 Answers1

0

I figured out how to execute App Script functions using Apps Script API. So I am posting the answer for the benefit of others. Also I would try to plug in the missing information which Google has not provided in their instructions.

  1. The target script is an App Script (e.g. "code.gs") file with the required functions to be executed. This script has to be attached to a GCP project with App Script API enabled.

  2. The calling script has to be an html file saved in a local folder and not an App Script file. Below is an example "index.html" which calls two functions "callScriptFunction()" and "getSheets()".

     <!DOCTYPE html>
 <html>
 <head>
    <title>Google Apps Script API Quickstart</title>
    <meta charset="utf-8" />
  </head>
  <body>
   <p>Google Apps Script API Quickstart</p>

 <!--Add buttons to initiate auth sequence and sign out-->
 <button id="authorize_button" style="display: none;">Authorize</button>
 <button id="signout_button" style="display: none;">Sign Out</button>

 <pre id="content" style="white-space: pre-wrap;"></pre>

 <script type="text/javascript">
   // Client ID and API key from the Developer Console
   var CLIENT_ID = 'YOUR_CLIENT_ID';
   var API_KEY = 'YOUR_API_KEY';

   // Array of API discovery doc URLs for APIs used by the quickstart
   var DISCOVERY_DOCS = ["https://script.googleapis.com/$discovery/rest?version=v1"];

   // Authorization scopes required by the API; multiple scopes can be
   // included, separated by spaces.
   var SCOPES = 'https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive.readonly';

   var authorizeButton = document.getElementById('authorize_button');
   var signoutButton = document.getElementById('signout_button');

   /**
    *  On load, called to load the auth2 library and API client library.
    */
   function handleClientLoad() {
     gapi.load('client:auth2', initClient);
   }

   /**
    *  Initializes the API client library and sets up sign-in state
    *  listeners.
    */
   function initClient() {
     gapi.client.init({
       apiKey: API_KEY,
       clientId: CLIENT_ID,
       discoveryDocs: DISCOVERY_DOCS,
       scope: SCOPES
     }).then(function () {
       // Listen for sign-in state changes.
       gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);

       // Handle the initial sign-in state.
       updateSigninStatus(gapi.auth2.getAuthInstance().isSignedIn.get());
       authorizeButton.onclick = handleAuthClick;
       signoutButton.onclick = handleSignoutClick;
     }, function(error) {
       appendPre(JSON.stringify(error, null, 2));
     });
   }

   /**
    *  Called when the signed in status changes, to update the UI
    *  appropriately. After a sign-in, the API is called.
    */
   function updateSigninStatus(isSignedIn) {
     if (isSignedIn) {
       authorizeButton.style.display = 'none';
       signoutButton.style.display = 'block';
    //   callScriptFunction();
       getSheets();
     } else {
       authorizeButton.style.display = 'block';
       signoutButton.style.display = 'none';
     }
   }

   /**
    *  Sign in the user upon button click.
    */
   function handleAuthClick(event) {
     gapi.auth2.getAuthInstance().signIn();
   }

   /**
    *  Sign out the user upon button click.
    */
   function handleSignoutClick(event) {
     gapi.auth2.getAuthInstance().signOut();
   }

   /**
    * Append a pre element to the body containing the given message
    * as its text node. Used to display the results of the API call.
    *
    * @param {string} message Text to be placed in pre element.
    */
   function appendPre(message) {
     var pre = document.getElementById('content');
     var textContent = document.createTextNode(message + '\n');
     pre.appendChild(textContent);
   }

   /**
    * Shows basic usage of the Apps Script API.
    *
    * Call the Apps Script API to create a new script project, upload files
    * to the project, and log the script's URL to the user.
    */
   function callScriptFunction() {
  var scriptId = "TARGET_SCRIPT_ID";

 // Call the Apps Script API run method
 //   'scriptId' is the URL parameter that states what script to run
 //   'resource' describes the run request body (with the function name
 //              to execute)
 gapi.client.script.scripts.run({
 'scriptId': scriptId,
 'resource': {
   'function': 'getFoldersUnderRoot',
   'devMode': true
    }
 }).then(function(resp) {
   var result = resp.result;
   if (result.error && result.error.status) {
   // The API encountered a problem before the script
   // started executing.
   appendPre('Error calling API:');
   appendPre(JSON.stringify(result, null, 2));
 } else if (result.error) {
   // The API executed, but the script returned an error.

   // Extract the first (and only) set of error details.
   // The values of this object are the script's 'errorMessage' and
   // 'errorType', and an array of stack trace elements.
   var error = result.error.details[0];
   appendPre('Script error message: ' + error.errorMessage);

   if (error.scriptStackTraceElements) {
     // There may not be a stacktrace if the script didn't start
     // executing.
     appendPre('Script error stacktrace:');
     for (var i = 0; i < error.scriptStackTraceElements.length; i++) {
       var trace = error.scriptStackTraceElements[i];
       appendPre('\t' + trace.function + ':' + trace.lineNumber);
     }
   }
 } else {
   // The structure of the result will depend upon what the Apps
   // Script function returns. Here, the function returns an Apps
   // Script Object with String keys and values, and so the result
   // is treated as a JavaScript object (folderSet).

   var folderSet = result.response.result;
   if (Object.keys(folderSet).length == 0) {
       appendPre('No folders returned!');
   } else {
     appendPre('Folders under your root folder:');
     Object.keys(folderSet).forEach(function(id){
       appendPre('\t' + folderSet[id] + ' (' + id  + ')');
     });
     }
   }
 });
 }



  function getSheets() {

   // ID of the script to call. Acquire this from the Apps Script editor,
   // under Publish > Deploy as API executable.
   var scriptId = "TARGET_SCRIPT_ID";

  // Initialize parameters for function call.
  var sheetId = "SPREADSHEET_ID";


 gapi.client.script.scripts.run({
   'scriptId': scriptId,
   'resource': {
   'function': 'getSheetNames',
   'parameters': [sheetId],
   'devMode': true
 }
 }).then(function(resp) {
 var result = resp.result;
 if (result.error && result.error.status) {
   // The API encountered a problem before the script
   // started executing.
   appendPre('Error calling API:');
   appendPre(JSON.stringify(result, null, 2));
 } else if (result.error) {
   // The API executed, but the script returned an error.

   // Extract the first (and only) set of error details.
   // The values of this object are the script's 'errorMessage' and
   // 'errorType', and an array of stack trace elements.
   var error = result.error.details[0];
   appendPre('Script error message: ' + error.errorMessage);

   if (error.scriptStackTraceElements) {
     // There may not be a stacktrace if the script didn't start
     // executing.
     appendPre('Script error stacktrace:');
     for (var i = 0; i < error.scriptStackTraceElements.length; i++) {
       var trace = error.scriptStackTraceElements[i];
       appendPre('\t' + trace.function + ':' + trace.lineNumber);
     }
   }
 } else {
   // The structure of the result will depend upon what the Apps
   // Script function returns. Here, the function returns an Apps
   // Script Object with String keys and values, and so the result
   // is treated as a JavaScript object (folderSet).

   var names = result.response.result;
   if (Object.keys(names).length == 0) {
       appendPre('No sheetnames returned!');
   } else {
     appendPre(names);
   }
  }
 });  
}

 </script>

 <script async defer src="https://apis.google.com/js/api.js"
   onload="this.onload=function(){};handleClientLoad()"
   onreadystatechange="if (this.readyState === 'complete') this.onload()">
 </script>
  </body>
 </html>

  3. Below is an example of target script.

      function getFoldersUnderRoot() {
   var root = DriveApp.getRootFolder();
   var folders = root.getFolders();
   var folderSet = {};
   while (folders.hasNext()) {
     var folder = folders.next();
     folderSet[folder.getId()] = folder.getName();
   }
   return folderSet;
  }

  function getSheetNames(sheetId) {
       var ss = SpreadsheetApp.openById(sheetId);
       var sheets = ss.getSheets();
       var names = sheets.map(function(sheet) {
         return sheet.getName();
       })
       return names;
     }

  4. From terminal change to the working directory and execute python3 -m http.server 8000. Open browser and load "http://localhost:8000/". Authourize and proceed.

  5. You need to whitelist "http://localhost:8000/" in the project credentials

  6. You need to add required scopes in OAuth Consent Screen of the project.

I am able to execute the function "getSheetNames()" but "getFoldersUnderRoot()" is throwing error: Exception: We're sorry, a server error occurred. Please wait a bit and try again. Executing from the script editor also gives the same error. However "getFoldersUnderRoot()" is executable on any other script which is not attached to a GCP project

Partha S. Pal
  • 169
  • 1
  • 2
  • 14
  • 1
    Have you enabled the [Google Drive API in the Google Cloud Platform's console](https://console.cloud.google.com/apis/library/drive.googleapis.com) for your project? – Martí Dec 29 '20 at 12:23
  • 1
    Thanks for the suggestion. Indeed enabling Google Drive API solved the problem. But why its not written in the instructions provided by Google? Step 3 point a says only to enable Script API. – Partha S. Pal Dec 29 '20 at 13:20