Google Apps Standalone Script to use Google Apps Script API to update many bound scripts

1
votes

I am trying to write a standalone Google Apps script that uses the Google Apps Script API to update the the bound script contents of many Google Sheets.

I have the Sheet IDs of roughly 200 Google Sheets that I've created from a template. I would like to update the project contents of the bound scripts on each of these sheets to be identical to a set of master scripts.

I am stuck with an authentication error while using the urlFetchApp to get the contents of the bound script of one sheet as a test. The error looks like:

Request failed for
https://script.googleapis.com/v1/projects/<SCRIPTID>/content returned code 401. 
Truncated server response: { "error": { "code": 401, 
"message": "Request is missing required authentication credential.
Expected OAuth 2 access token, login cookie ... 
(use muteHttpExceptions option to examine full response) (line 34, file "AddScriptsToSheets")

The test function I'm using looks like:

function getSheetScriptContent(sheetId) { 
  var sheet = SpreadsheetApp.openById(sheetId);
  // Make a POST request with a JSON payload.
  // Make a GET request and log the returned content.
  var url = PROJECTS_GET_CONTENT_URL.format(sheetId);
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

I think this OAuth2 library may be useful in this case, I'm just not sure how to use it. Could anyone point me in the right direction?

1
google-apps-scriptgoogle-apps-script-api
You need to supply an authorization header in your urlfetch line. You don't need a library for this if the executing user has edit access to the documents. – tehhowch
tools.ietf.org/html/rfc6750#page-5 developers.google.com/apps-script/reference/url-fetch/… developers.google.com/apps-script/reference/script/script-app – tehhowch
Related:stackoverflow.com/a/52505621 – TheMaster
For example, is this GAS library useful for your situation? github.com/tanaikech/ProjectApp2 – Tanaike
I've filed an IssueTracker request for a feature that would make this task much simpler: issuetracker.google.com/issues/117105106 – tehhowch

1 Answers

3
votes

If you own all the files, then you don't need to use an OAuth library or any special code to get the access token. You can get the access token from the ScriptApp class.

var theAccessTkn = ScriptApp.getOAuthToken();

You may need to manually edit the appsscript.json manifest file and add the scope:

https://www.googleapis.com/auth/script.projects

appsscript.json

{
  "timeZone": "Yours will display here",
  "dependencies": {
  },
  "webapp": {
    "access": "MYSELF",
    "executeAs": "USER_DEPLOYING"
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/drive", 
    "https://www.googleapis.com/auth/script.projects", 
    "https://www.googleapis.com/auth/drive.scripts", 
    "https://www.googleapis.com/auth/script.container.ui", 
    "https://www.googleapis.com/auth/script.external_request"
  ],
  "runtimeVersion": "DEPRECATED_ES5"
}

Code to overwrite an Apps Script file:

function updateContent(scriptId,content,theAccessTkn) {
try{
  var options,payload,response,url;

  if (!content) {
    //Error handling function
    return;
  }
  
  if (!theAccessTkn) {
    theAccessTkn = ScriptApp.getOAuthToken();
  }
  
  //https://developers.google.com/apps-script/api/reference/rest/v1/projects/updateContent
  url = "https://script.googleapis.com/v1/projects/" + scriptId + "/content";

  options = {
    "method" : "PUT",
    "muteHttpExceptions": true,
    "headers": {
      'Authorization': 'Bearer ' +  theAccessTkn
     },
    "contentType": "application/json",//If the content type is set then you can stringify the payload
    "payload": JSON.stringify(content)
  };
  
  response = UrlFetchApp.fetch(url,options);      
  //Logger.log('getResponseCode ' + response.getResponseCode())
  //Logger.log("Response content: " + response.getContentText())

} catch(e) {
  console.log("Error: " + e + "\nStack: " + e.stack)
}
};