Use App Script to carry out one function, once, on many spreadsheets

Question

I want to run a Google Script which normalises a cell range, on a number (30+) of Google Sheets. Or more precisely, I want a (less technical) user to be able to do this. I can't seem to find a reasonable workflow.

The options I can see are:

Copy/paste the script as a bound script to each of the spreadsheets

That's messy because then there are many copies of the script, which generally won't be run again, and there's a lot of overhead and clicking around to install the macro for each one.

Use a library

I could put the body of the code in a library, then make the copy/paste just a stub (like the accepted answer here ).

However, that's still just as bad for the UX, plus various reports that libraries are messy to deal with.

Make an add-on

The "right way" seems to be to create an add-on which the user can enable for each spreadsheet. However, add-ons still seem to be in "developer preview" mode, and the authorisation cycle is uncertain and potentially slow. Google also expects that The script has been tested with multiple active users. which would be hard - by the time I'd tested it this thoroughly, the job would basically be done. And how would I test it without publishing the add-on anyway?

Other options?

Is there some other way, perhaps using an unbound-script? It's not possible to run a single script once and have it iterate over all the spreadsheets as a bit of user input is required (which range within the spreadsheet etc).

Is there a way where the user could install an unbound script, run it, and it would ask which spreadsheet to run it on?

openByUrl() is really close, but it doesn't actually open the spreadsheet UI, so I wouldn't be able to use functions like getActiveRange() etc.

In case it's relevant, here's the script:

/*function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Normalize')
      .addItem('Normalize Crosstab', 'normalizeCrossTab')
      .addToUi();
}*/

function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var items = [
    {name: 'Normalize Crosstab', functionName: 'normalizeCrosstab'},
  ];
  ss.addMenu('Normalize', items);
}

/* Converts crosstab format to normalized form. Given columns abcDE, the user puts the cursor somewhere in column D.
The result is a new sheet, NormalizedResult, like this:

a     b     c    Field Value
a1    b1    c1   D     D1
a1    b1    c1   E     E1
a2    b2    c2   D     D2
a2    b2    c2   E     E2
...

*/
function normalizeCrosstab() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var firstDataCol = SpreadsheetApp.getActiveRange().getColumn();
  var dataCols = values[0].slice(firstDataCol-1);

  if (Browser.msgBox("This will create a new sheet, NormalizedResult. Place your cursor is in the first data column.\\n\\n" +
                     "These will be your data columns: " + dataCols,Browser.Buttons.OK_CANCEL) == "cancel") {
    return;
  }


  var resultssheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NormalizedResult");
  if (resultssheet != null) {
    SpreadsheetApp.getActive().deleteSheet(resultssheet);
  }
  var header = values[0].slice(0, firstDataCol - 1);

  var newRows = [];
  
  header.push("Field");
  header.push("Value");
  newRows.push(header);

  for (var i = 1; i <= numRows - 1; i++) {
    var row = values[i];
    for (var datacol = 0; datacol < dataCols.length; datacol ++) {
      newRow = row.slice(0, firstDataCol - 1); // copy repeating portion of each row
      newRow.push(values[0][firstDataCol - 1 + datacol]); // field name
      newRow.push(values[i][firstDataCol - 1 + datacol]); // field value
      newRows.push(newRow);
    }
  }
  var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("NormalizedResult");
  var r = newSheet.getRange(1,1,newRows.length, header.length);
  r.setValues(newRows);
};

Answer 1

The first question is: "Who is the owner of all these sheets?" If you are the owner of all these sheets, then you have permission to access them remotely. If you don't own them, then the owner would need to share, and give editing permissions to whatever code is trying to modify their file.

If you own all the spreadsheets, you could create a Stand Alone App do all the processing from a central point. Then you can just email the link of the Stand Alone App to everyone, or have each user enter a link in their spreadsheet to the Stand Alone App. As you mentioned, for that option you won't be able to use methods like, getActiveSheet().

No matter what option you use, you'll need to either have people add something to their spreadsheet, or create some new, centralized interface. The best option for you may come down to ownership and setting permissions.

I'm guessing that if the users of the spreadsheets are the owners, and don't want to give you permission, they'll need to use one of your first three options. And I'd start with the library first.

If you can easily get the file ID's of the spreadsheets, you could create an object that matches the user to the FileID.

var objUserToFileID = {"user1":"abc34ciu89384u", "user2":"FileID_Two", "user3":"FileID_Three"};

Then have a way for the user to choose their name from the list, (Drop Down List) then run the code. That's for the Stand Alone App. Of course, then you'd need to figure out what happens if the user chooses the fileID for someone else's spreadsheet. Then you'd need to have a way to determine who the user of the App is.