Google-sheets – How to run script on multiple Google Sheet tabs

google sheetsgoogle-apps-script

Here's my code

function saveRange(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheetByName('Sample_tab_1');
  var range=sheet.getRange('A8:AE47').getValues();

  sheet.getRange(sheet.getLastRow()+1, 1, range.length, range[0].length).setValues(range);  
}

But let's say I want to run this on not just Sample_tab_1 but also 2, 3, 4, etc. (not all of the tabs in the entire sheet though, just the ones I specify.)

Best Answer

An easy way to do that would be to save the tabs that you want to run the script on in an array and then use a for loop to cycle through that array and run the code on each specified tab.

It would look something like this:

function saveRange(){
    var tabs = [
        'Sample_tab_1',
        'Sample_tab_2',
        'Sample_tab_3',
        'Sample_tab_4'
    ];

    var ss=SpreadsheetApp.getActiveSpreadsheet();
    for (var i = 0; i < tabs.length; i++) {
        var sheet=ss.getSheetByName(tabs[i]);
        var range=sheet.getRange('A8:AE47').getValues();

        sheet.getRange(sheet.getLastRow()+1, 1, range.length, range[0].length).setValues(range);
    }
  }

Related Solutions

Google-sheets – How to trigger a function when switching sheets within a spreadsheet

As Rubén says, there's no such event built in. But you might have some (limited) success with this approach:

var PROP_NAME = "lastSheetIdx";
var userProperties = PropertiesService.getUserProperties();

function didSwitchSheets(from, to) {
  Logger.log("Switched from " + from + " to " + to);
  // Your code here
}

function timedEventHandler() {
  var currentSheetIdx = SpreadsheetApp.getActiveSheet().getIndex()
  var previousSheetIdx = parseInt(userProperties.getProperty(PROP_NAME));
  if (currentSheetIdx !== previousSheetIdx) {
    didSwitchSheets(previousSheetIdx, currentSheetIdx);
    userProperties.setProperty(PROP_NAME, currentSheetIdx);
  }
}

Paste this code, press the Run button to authorize the script (you only need to do this once).
Then, from the Resources menu, select Triggers for current script, and set up the timedEventHandler to run as often as you can - which currently seems to be "every minute".

That will cause the function to run every minute and check if the "current sheet" id has changed, and if so, the didSwitchSheets function will run.

Probably, one minute is not often enough. But it might look as though Google is about to implement the possibility to run a script every second. At least that comes up as a choice in my Triggers for current script dialog, but it does not seem to work when I select it - see Can I set a trigger to run every second?

I have created an example spreadsheet to demonstrate this. Not much to see there, but make a copy of it (File → Copy) to your own Google Drive, and you should be able to see and edit the script as much as you like.

Google Sheets – Copying Last Row to Another Spreadsheet on Form Submission

Just check to see if the first item in the sourceData Array is not the same value that is in A1 before you copy the data over. I am assuming this is a title row and that you don't have anyone named "name" in your data set.

var sourceData = sheet.getRange(lastrow, 1,1, 37).getValues();
if (sourceData[0][0] != sheet.getRange(1, 1).getValue();){
  target.appendRow(sourceData[0]);
  }
}

Best Answer

Related Solutions

Google-sheets – How to trigger a function when switching sheets within a spreadsheet

Google Sheets – Copying Last Row to Another Spreadsheet on Form Submission

Related Topic