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": 0 , "Sheet ID": 1, "Script ID": 2,
  "Last Update": 3, "Error": 4
};

// 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(Object.keys(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,HEADERS["Error"]+1).setValue("ERROR: " + err); // values are 0-based, but sheet is 1-based
      }
    }
  }
}

  
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;
}

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

Full Source

var HEADERS = {
  "Sheet Name": 0 , "Sheet ID": 1, "Script ID": 2,
  "Last Update": 3, "Error": 4
};

// 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,HEADERS["Error"]+1).setValue("ERROR: " + err); // values are 0-based, sheet is 1-based
      }
    }
  }
}

  
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;
}

Credits

• Jan 15, 2021: Thanks to Liam Abbott for highlighting some errors with the original script. These have now been fixed
  • Off by one error on this line:  sh.getRange(i+1,4).setValue("ERROR: " + err);
  • Headers array should have been an object, where the keys are the column names and the values are the column position.