Managing Multiple Script Projects Using the Google Apps Script API


This article will discuss how to manage multiple, container-bound script projects using the Google Apps Script API from within Google Apps Script itself.

I was working for a client who owns several hundred worksheets and does not use Google Suite. Their employees interact with these sheets daily and they wanted me to automate some of their workflow. The workflows required access to each sheet that only container-bound scripts could afford.

Since their Google Drive Files were owned by a personal Google account, add-ons could not be installed domain wide.

There were a couple ways to solve this and I list some of the alternatives at the bottom of this article. I decided to use a source script project, the Google Apps Script API and a helper Google Script to create and update the script projects.

The workflow to create everything looks like this:

  • Traverse a Google Drive folder to aggregate all file IDs
  • List these IDs in a new Google Spreadsheet (Master List)
  • Iterate the Master List and:
    • Create a new container-bound script project
    • Copy the source project to the new script project
    • Record the new script project ID in the Master List
    • Record the Date created or any errors encountered

Updates follow a similar pattern:

  • Iterate the Master List and:
    • If a script project ID exists, update it and:
      • Record the date updated and success
    • Otherwise:
      • Create a new container-bound script project
      • Copy the source project to the new script project
      • Record the new script project ID in the Master List
      • Record the Date created or any errors encountered

This article will assume that you already have a script project who’s code you want to add to multiple Google Files. Take note of this script project’s ID for future reference.

File > Project Properties > Script ID

We will build the remaining requirements in the following steps.

Getting Started


We’re going to create a Google Script Project where we manage the aggregation of File IDs into a Master List, Iteration of that Master List and Creation/Update of the Script Projects via the Google Apps Script API.

Create a new Script Project and name it to your liking. Create a new Spreadsheet and do the same, taking note of the spreadsheet URL. Rename the worksheet in the spreadsheet as well. Remember the URL and worksheet name for later.

First we’re going to populate our spreadsheet with the file ids of all Google files who’s script projects we want to manage. The function below assumes all the file IDs you want to collect are in a single folder. If you need to modify this function to run on multiple folders or to be called regularly, reach out to me for assistance: jc@jcconnell.com.

Add the function below to your script project and run it:

var HEADERS = ['Sheet Name', 'Sheet ID', 'Script ID', 'Last Update', 'Error'];

// EDIT THESE
var dest_sht_url = "YOUR_SPREADSHEET_URL_HERE";
var dest_wks_nme = "YOUR_WORKSHEET_NAME_HERE";
var src_fldr_id = "YOUR_SOURCE_FOLDER_ID_HERE";
var src_prjct_id = "YOUR_SOURCE_PROJECT_ID_HERE";

// DON'T EDIT THIS  
function getFileIDsInFolderNoSubFolders() {
  
  var sh = SpreadsheetApp.openByUrl(dest_sht_url).getSheetByName(dest_wks_nme);
  var folder = DriveApp.getFolderById(src_fldr_id);
  
  var list = [];
  list.push(HEADERS);
  
  var files = folder.getFiles();
  while (files.hasNext()){
    file = files.next();
    var row = [];
    row.push(file.getName(),file.getId(),"", "", "");
    list.push(row);
  }
   sh.getRange(1,1,list.length,list[0].length).setValues(list);
}

Great! Now you have a spreadsheet with the IDs of all the spreadsheets you want to manage. Next, we’ll write some helper code to traverse this information and either create or update the script project associated with each Google file.

Managing Each Sheet’s Script Project


Now that we have our list of file IDs, we’re ready to write code that will create or update a script project for each file. Copy the following function into the bottom of your script project.

The function below is a bit long, so review the notes for a better understanding of what each line is doing.

function createOrUpdateScripts() {

  var start = new Date(); // When the function started processing
  var sh = SpreadsheetApp.openByUrl(dest_sht_url).getSheetByName(dest_wks_nme);
  var values = sh.getDataRange().getValues();
  
  // Create date one week ago
  var one_week = new Date();
  one_week.setDate(one_week.getDate() - 7);
  
  for(var i=1;i<values.length;i++) { // 0-based array, start at 1 to skip headers
    
    // Check if the Sheet ID field is empty and skip row if true
    var empty_file_id = values[i][HEADERS["Sheet ID"]] == "" ? true : false;
    if( empty_file_id ) {
      Logger.log("Skipped row: " + i);
      continue;
    }
    
    // Get the last update
    //   If empty or invalid, set to an arbitrary historical date
    var last_update = values[i][HEADERS["Last Update"]];
    if( last_update === "" || !isValidDate(last_update) ) {
        last_update = new Date("Jan 1, 1970");
    }
    
    // Was there an error?
    var error_on_last = values[i][HEADERS["Error"]] ? 
        values[i][HEADERS["Error"]].toString().toLowerCase().indexOf("error") : -1;
        
     
    // Update once a week or less and retry errors
    if( last_update.getTime() < one_week.getTime() || error_on_last != -1 ) {
      
      // Are we about to run out of time?
      if (isTimeUp_(start)) {
        Logger.log("Time up");
        break;
      }
      
      var timestamp = new Date();
      try {
        // Create the script project
        var dst_prjct_id = values[i][HEADERS["Script ID"]];
        if( dst_prjct_id === "" ) {
          var dst_script_id = copySourceToDestination(src_prjct_id, dst_prjct_id);
          
          // Set script ID, timestamp of last update and clear errors 
          var lastRun = [dst_script_id, timestamp, ""];
          sh.getRange(i+1,2,1,3).setValues([lastRun]);
        }
        else {
          // Update the Script Project
          var upd_script_id = values[i][HEADERS["Script ID"]];
          var dst_script_id = updateDestinationWithSource(src_prjct_id, upd_script_id);
          
          // Set script ID, timestamp of last update and clear errors
          var lastRun = [dst_script_id, timestamp, ""];
          sh.getRange(i+1,2,1,3).setValues([lastRun]);
        }
      } 
      catch(err) {
        sh.getRange(i+1,4).setValue("ERROR: " + err);
      }
    }
  }
}

  
function isValidDate(d) {
  return d instanceof Date && !isNaN(d);
}

Now we can aggregate file ids and manage the creation and update of their associated script projects but we’re still missing two pieces. How do we create and update their script projects? We’re almost there.

Create Script Project via File ID And Copy Source Project Content


The next function will create a container-bound script project using a file ID. Then it will copy the contents of a source script project to the new container-bound script project and return the ID. You’ll need to complete a few steps before it’s functional.

  1. Enable the Google Apps Script API if you haven’t already
    1. Click here to enable
  2. Add the following scopes to your manifest file: View > Show Manifest
    1. https://www.googleapis.com/auth/script.projects
    2. https://www.googleapis.com/auth/script.external_request

Copy the following function into the bottom of your script project.

function copySourceToDestination(srcProjectId, destFileId) {

  var baseUrl = "https://script.googleapis.com/v1/projects";
  var accessToken = ScriptApp.getOAuthToken();

  // Retrieve filename of bound-script project.
  var srcName = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + srcProjectId, {
    method: "get",
    headers: {"Authorization": "Bearer " + accessToken}
  }).getContentText()).title;

  // Retrieve bound-script project.
  var obj = UrlFetchApp.fetch(baseUrl + "/" + srcProjectId + "/content", {
    method: "get",
    headers: {"Authorization": "Bearer " + accessToken}
  }).getContentText();

  // Create new bound script and retrieve project ID.
  var dstId = JSON.parse(UrlFetchApp.fetch(baseUrl, {
    method: "post",
    contentType: 'application/json',
    headers: {"Authorization": "Bearer " + accessToken},
    payload: JSON.stringify({"title": srcName, "parentId": destFileId})
  }).getContentText()).scriptId;

  // Upload a project to bound-script project.
  var res = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + dstId + "/content", {
    method: "put",
    contentType: 'application/json',
    headers: {"Authorization": "Bearer " + accessToken},
    payload: obj
  }).getContentText());

  Logger.log(dstId);
  return dstId;
}

Now we can create container-bound scripts associated with specific file IDs but what if the file already has a script project? How do we update it? That’s what we’ll review in the next section.

Update Script Project via Script ID And Copy Source Project Content


This is the last step. Copy the following function to the bottom of your script project.

function updateDestinationWithSource(srcProjectId, destProjectId) {

  var baseUrl = "https://script.googleapis.com/v1/projects";
  var accessToken = ScriptApp.getOAuthToken();

  // Retrieve bound-script project.
  var obj = UrlFetchApp.fetch(baseUrl + "/" + srcProjectId + "/content", {
    method: "get",
    headers: {"Authorization": "Bearer " + accessToken}
  }).getContentText();

  // Upload a project to bound-script project.
  var res = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + destProjectId + "/content", {
    method: "put",
    contentType: 'application/json',
    headers: {"Authorization": "Bearer " + accessToken},
    payload: obj
  }).getContentText());
  
  Logger.log(destProjectId);
  return destProjectId;
}

Tying It All Together


Now that we have all our code in place we’re ready to put it all together. Run the createOrUpdateScript() function. If you have more than around 50 file IDs in your spreadsheet, it’s very likely that you will hit either an API limit or a quota. Wait until the function is finished running and then check your master list.

If there were any errors, they will be recorded on your master list and subsequent runs of the function will retry them. Wait a few minutes and then run the function again. Repeat until all of your file’s have a container-bound script associated with them. If you find yourself experiencing any errors, check the logs or reach out to me for some help.

That’s it! You can read about some of the alternatives below and at the very bottom you can see the script in it’s entirety below.

If you have any questions or would like some help with a custom implementation, reach out to me for some help: jc@jcconnell.com

Alternatives


Installable Triggers

If you’re managing fewer than 20 worksheets and wish to use a single container-bound script project for all of them, you might investigate installable triggers. This has the benefit of container-bound features and a single source project where you can manage the code. However, it’s restricted to fewer than 20 total triggers. That means if you need both onOpen() and onEdit() triggers for a single sheet, you’ll be limited to managing only 10 sheets with this method.

Clasp

You can use Google’s Clasp command line tool to push a single source script project to multiple destination script projects using the setting feature. An example workflow would use a bash script like the following to push a single script project to multiple script IDs.

Note: if you have a list of Drive IDs of parent files that the created script projects should be bound to, you’ll first need to create the script project and parse the output for the project ID.

clasp setting scriptId <id>
clasp push
clasp setting scriptId <id2>
clasp push

Full Source


var HEADERS = ['Sheet Name', 'Sheet ID', 'Script ID', 'Last Update', 'Error'];

// EDIT THESE
var dest_sht_url = "YOUR_SPREADSHEET_URL_HERE";
var dest_wks_nme = "YOUR_WORKSHEET_NAME_HERE";
var src_fldr_id = "YOUR_SOURCE_FOLDER_ID_HERE";
var src_prjct_id = "YOUR_SOURCE_PROJECT_ID_HERE";

// DON'T EDIT THIS  
function getFileIDsInFolderNoSubFolders() {
  
  var sh = SpreadsheetApp.openByUrl(dest_sht_url).getSheetByName(dest_wks_nme);
  var folder = DriveApp.getFolderById(src_fldr_id);
  
  var list = [];
  list.push(HEADERS);
  
  var files = folder.getFiles();
  while (files.hasNext()){
    file = files.next();
    var row = [];
    row.push(file.getName(),file.getId(),"", "", "");
    list.push(row);
  }
   sh.getRange(1,1,list.length,list[0].length).setValues(list);
}

function createOrUpdateScripts() {

  var start = new Date(); // When the function started processing
  var sh = SpreadsheetApp.openByUrl(dest_sht_url).getSheetByName(dest_wks_nme);
  var values = sh.getDataRange().getValues();
  
  // Create date one week ago
  var one_week = new Date();
  one_week.setDate(one_week.getDate() - 7);
  
  for(var i=1;i<values.length;i++) { // 0-based array, start at 1 to skip headers
    
    // Check if the Sheet ID field is empty and skip row if true
    var empty_file_id = values[i][HEADERS["Sheet ID"]] == "" ? true : false;
    if( empty_file_id ) {
      Logger.log("Skipped row: " + i);
      continue;
    }
    
    // Get the last update
    //   If empty or invalid, set to an arbitrary historical date
    var last_update = values[i][HEADERS["Last Update"]];
    if( last_update === "" || !isValidDate(last_update) ) {
        last_update = new Date("Jan 1, 1970");
    }
    
    // Was there an error?
    var error_on_last = values[i][HEADERS["Error"]] ? 
        values[i][HEADERS["Error"]].toString().toLowerCase().indexOf("error") : -1;
        
     
    // Update once a week or less and retry errors
    if( last_update.getTime() < one_week.getTime() || error_on_last != -1 ) {
      
      // Are we about to run out of time?
      if (isTimeUp_(start)) {
        Logger.log("Time up");
        break;
      }
      
      var timestamp = new Date();
      try {
        // Create the script project
        var dst_prjct_id = values[i][HEADERS["Script ID"]];
        if( dst_prjct_id === "" ) {
          var dst_script_id = copySourceToDestination(src_prjct_id, dst_prjct_id);
          
          // Set script ID, timestamp of last update and clear errors 
          var lastRun = [dst_script_id, timestamp, ""];
          sh.getRange(i+1,2,1,3).setValues([lastRun]);
        }
        else {
          // Update the Script Project
          var upd_script_id = values[i][HEADERS["Script ID"]];
          var dst_script_id = updateDestinationWithSource(src_prjct_id, upd_script_id);
          
          // Set script ID, timestamp of last update and clear errors
          var lastRun = [dst_script_id, timestamp, ""];
          sh.getRange(i+1,2,1,3).setValues([lastRun]);
        }
      } 
      catch(err) {
        sh.getRange(i+1,4).setValue("ERROR: " + err);
      }
    }
  }
}

  
function isValidDate(d) {
  return d instanceof Date && !isNaN(d);
}

function copySourceToDestination(srcProjectId, destFileId) {

  var baseUrl = "https://script.googleapis.com/v1/projects";
  var accessToken = ScriptApp.getOAuthToken();

  // Retrieve filename of bound-script project.
  var srcName = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + srcProjectId, {
    method: "get",
    headers: {"Authorization": "Bearer " + accessToken}
  }).getContentText()).title;

  // Retrieve bound-script project.
  var obj = UrlFetchApp.fetch(baseUrl + "/" + srcProjectId + "/content", {
    method: "get",
    headers: {"Authorization": "Bearer " + accessToken}
  }).getContentText();

  // Create new bound script and retrieve project ID.
  var dstId = JSON.parse(UrlFetchApp.fetch(baseUrl, {
    method: "post",
    contentType: 'application/json',
    headers: {"Authorization": "Bearer " + accessToken},
    payload: JSON.stringify({"title": srcName, "parentId": destFileId})
  }).getContentText()).scriptId;

  // Upload a project to bound-script project.
  var res = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + dstId + "/content", {
    method: "put",
    contentType: 'application/json',
    headers: {"Authorization": "Bearer " + accessToken},
    payload: obj
  }).getContentText());

  Logger.log(dstId);
  return dstId;
}

function updateDestinationWithSource(srcProjectId, destProjectId) {

  var baseUrl = "https://script.googleapis.com/v1/projects";
  var accessToken = ScriptApp.getOAuthToken();

  // Retrieve bound-script project.
  var obj = UrlFetchApp.fetch(baseUrl + "/" + srcProjectId + "/content", {
    method: "get",
    headers: {"Authorization": "Bearer " + accessToken}
  }).getContentText();

  // Upload a project to bound-script project.
  var res = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + destProjectId + "/content", {
    method: "put",
    contentType: 'application/json',
    headers: {"Authorization": "Bearer " + accessToken},
    payload: obj
  }).getContentText());
  
  Logger.log(destProjectId);
  return destProjectId;
}

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.