update google sheet script with another script

Question

I have a folder with approximately 140 google sheets and a main standalone google script that I call with a library script from each sheet but need to add/update all of the scripts attached to each google sheet script. Currently the only way I have found is to open each sheet script and add the library script save and move on but 140 sheets takes a long time. I know all my sheets that i need scripts adding or updating are in one folder so thinking I could use something like this to loop through all the gsheets but can't find away to edit the scripts from here...

function scriptupdate() {
  var folder = DriveApp.getFolderById('FOLDER CONTAINING THE GSHEETS ID');
  var files = folder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    Logger.log("File Name is "+file.getName());
    Logger.log("File ID is "+file.getId());
  }
}

I'm not sure if what I'm trying to do is possible but trying to save a lot of time if this is doable but really appreciate any help and guidance offered

In the current stage, in order to create and update the container-bound script, it is required to use Google Apps Script API. If your Spreadsheets have already had the container-bound script, you can update it using Method: projects.updateContent. If your Spreadsheets have no the container-bound script, you can create the container-bound script and update it using Method: projects.create and Method: projects.updateContent. — Tanaike
Great. Thank you for the pointers, I'll now start to learn how to use the API functions — Big Al
Have you been able to solve your problem? If so, it would be good for the community if you could share your solution, otherwise I would be happy to help you do so. — fullfine

fullfine fullfine · Accepted Answer · 2021-03-23T16:30:59

Answer

You can update create and update projects and its content with some methods of the projects resource. As these have no built-in service in Apps Script they must be called with urlFetchApp.fetch.

When writing functions, remember to put them between grave accents (`). You can use the key combination Ctrl + Shift + I to fix all the indentation and spacing in the whole script file.

Code

function scriptupdate() {

  // GET FILES
  var folder = DriveApp.getFolderById('FOLDER CONTAINING THE GSHEETS ID');
  var files = folder.getFiles();
  while (files.hasNext()) {

    // GET FILE INFORMATION
    var file = files.next();
    var name = file.getName()
    var fileId = file.getId()

    // CREATE CONTAINER-BOUND SCRIPT
    var url = 'https://script.googleapis.com/v1/projects'
    var formData = {
      'title': name,
      'parentId': fileId
    };

    var options = {
      method: 'post',
      headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
      payload: formData,
      muteHttpExceptions: true
    };

    var res = JSON.parse(UrlFetchApp.fetch(url, options).getContentText())
    var scriptId = res["scriptId"]


    // UPDATE PROJECT CONTENT
    var url = 'https://script.googleapis.com/v1/projects/' + scriptId + '/content';

    var formData = {
      "files": [
        {
          "name": "appsscript",
          "type": "JSON",
          "source": `{
          "timeZone": "America/New_York",
          "dependencies": {},
          "exceptionLogging": "STACKDRIVER",
          "runtimeVersion": "V8"
          }`
        },
        {
          "name": "main",
          "source":
            `function main() {
              console.log('hello world')
            }`,
          "type": "SERVER_JS"
        }
      ]
    }

    var options = {
      method: 'put',
      contentType: 'application/json',
      payload: JSON.stringify(formData),
      headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
      muteHttpExceptions: true
    };

    var res = UrlFetchApp.fetch(url, options).getContentText()
    console.log(res)

  }
}

update google sheet script with another script

1 Answers

Answer

Code

Reference