Help! I have one standalone script, which I want to be called in 50 separate google spreadsheets onOpen of each of these spreadsheets. I need a web app or something that will run this script as myself, not the user opening one of those 50 sheets, as the script is referring to some other files, to which the users do not have access. **My problem I do know how to make this standalone script run when one of the 50 files is opened - should I put some code (like doGet()) in my spreadsheet bound script orwhere & what???**And should I put something in my standalone script? And can I do this avoiding creating HTML pages etc.? Thanks!

My standalone script that I want to apply to my multiple spreadsheets (in short it gets a name from a cell from the opened spreadsheet, creates a copy of a separate master spreadsheet, put the saved name to the master spsheet, recalculates there, put its back to the open spreadsheet (where the name was initially):

var masterFileId = "1i-------------------DM";
var tempHolderFolder = "1D---------------ae";

function ImportIndReport2(personalFileId) {
  var mf = SpreadsheetApp.openById(masterFileId);
  var destFolder = DriveApp.getFolderById(tempHolderFolder);
  var indCopyTest = DriveApp.getFileById(mf.getId()).makeCopy("test temp2", destFolder);
  var URLCopy = indCopyTest.getUrl();
  var currentIdToUse = SpreadsheetApp.openById(personalFileId).getSheetByName('Welcome').getRange('D6').getValue();
  SpreadsheetApp.openById(indCopyTest.getId()).getSheetByName("Welcome").getRange("C6").setValue(currentIdToUse);

  var now = new Date();
  SpreadsheetApp.openById(indCopyTest.getId()).getSheetByName("jj").getRange("A1").setValue(now);

  var oldSheet = SpreadsheetApp.openById(personalFileId).getSheetByName("jj");
  SpreadsheetApp.openById(personalFileId).deleteSheet(oldSheet);
  var CurrentIdDestination = SpreadsheetApp.openById(personalFileId);
  var indReportToCopy = SpreadsheetApp.openById(indCopyTest.getId()).getSheetByName('jj');
  SpreadsheetApp.openById(indCopyTest.getId()).insertSheet('Temp');
  var tempDestination = SpreadsheetApp.openById(indCopyTest.getId());
  indReportToCopy.getRange("A1:Z30").copyTo(tempDestination.getSheetByName('Temp').getRange("A1:Z30"),SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
  indReportToCopy.getRange("A1:Z30").copyTo(tempDestination.getSheetByName('Temp').getRange("A1:Z30"),SpreadsheetApp.CopyPasteType.PASTE_FORMAT,false);

  tempDestination.getSheetByName('Temp').copyTo(CurrentIdDestination).setName('jj');
  var tempDestinationForDelete = DriveApp.getFileById(indCopyTest.getId());
  tempDestinationForDelete.setTrashed(true);

  var html = HtmlService.createHtmlOutput("<div>The report has been updated</div>").setSandboxMode(HtmlService.SandboxMode.IFRAME).setWidth(150).setHeight(100);

  SpreadsheetApp.getUi().showModalDialog(html, "Message");
}

CodePudding user response：

Instead of using a "web app" it might be better to use an installable on open trigger, but installable triggers are limited to 20 per script / user.

You might create three stand alone projects to hold the triggers and put the main code on a fourth stand alone project to be used as library on the three projects.

Another option is to create and add-on. This might imply extra work but it will be done only once and will be easier to maintain.

Reference

• https://developers.google.com/apps-script/guides/triggers/installable

CodePudding user response：

Updated - solved it with clickable url of the webapp script in the individual spreadsheet bound script, and adding simple doGet() with name of my main function (indreport..) to standalone script.