Google-sheets – How to use the Google scripts on all the sheets

google sheetsgoogle-apps-script

I created a Google Sheets script (actually copied and pasted it).

It works on the sheet I was when I created the script, but not on others.

Is there a way to "activate" the script to another sheet?
Is there a way to make the script global to all my sheets?

I do not wish to copy and paste it to multiple sheets if avoidable, and I do not wish to publish it because I do not have the rights to it.

Best Answer

I think there are a couple of options; they might not be ideal but could work.

If by "to activate" the script you mean to authorize it, you can make a trigger that auto runs a function inside the script once the spreadsheet is open https://developers.google.com/apps-script/guides/triggers/

Having said that, to run your script in another spreadsheet and make it globally available to you, you could use libraries; take a look at this other link https://developers.google.com/apps-script/guides/libraries.

First you create your library in a separate script (this would need copy paste).
Once you have your library, you can import it to other Apps Script projects or spreadsheets.

The downside to this is that you would still need to open the script editor and write minimal code to import your library, so it might not be exactly what you want but I hope it helps.

Related Solutions

Google-sheets – Copy submitted form data to new automatically generated Google sheet

I have expanded the functionality of the script from the linked question accordingly. First it checks whether the sheet with given name already exists. If it doesn't, the sheet is created and the headers of the form responses sheet are copied there. Otherwise, the existing sheet it used. Either way, the newly submitted data is then copied to the sheet.

function copyData(e) {
  var sheetName = e.namedValues['Name'][0];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    var formSheet = e.range.getSheet();
    var headers = formSheet.getRange(1, 1, 1, formSheet.getLastColumn());
    headers.copyTo(sheet.getRange(1, 1));
  }
  sheet.appendRow(e.values);
}

The function must be equipped with a trigger using "Resources -> current project's triggers" in the Script Editor menu: the trigger options are "from spreadsheet", "on form submit".

Google-sheets – How to pull data from multiple sheets in Google Spreadsheets 1 and transfer to multiple sheets in Google Spreadsheets 2

Copying data from other spreadsheets is certainly possible with the IMPORTRANGE formula. Example usage form one of my spreadsheets:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/35A4onc5he6l-dy3K_R6zMPd2Nc7o4LjFFEQA8Cx0hCE","April!A2:AH6")

Don't know how to answer about the automation of doing this only at a specified time during the day. Normally IMPORTRANGE works as soon as you open the target spreadsheet.

Best Answer

Related Solutions

Google-sheets – Copy submitted form data to new automatically generated Google sheet

Google-sheets – How to pull data from multiple sheets in Google Spreadsheets 1 and transfer to multiple sheets in Google Spreadsheets 2

Related Topic