0

Thanks in advance for the help. I am currently running a Google Sheets script which applies to one tab called "Tab 1". It copies a range across to the next free column as follows:

function copyAcross() {
  var ss = SpreadsheetApp.getActive();
  var namesSheet = ss.getSheetByName('Tab 1')
  var names = namesSheet.getRange('A9:A12').getValues().filter(function (r) {return r[0]})
  namesSheet.getRange(9, namesSheet.getLastColumn() +1, names.length, 1).setValues(names)
}

I would like this script to run on ALL tabs EXCEPT one tab called "Targets".

Here is an example of the Sheet: https://docs.google.com/spreadsheets/d/1MUFR7XjSxiu_fe86fZjdQrm1fPjMLBvi8rxw3HkvKbs/edit?usp=sharing

0

You will have to loop over the sheets and use some criteria to decide whether to do the copy on a given sheet. Perhaps it would be easiest to use a regular expression to match sheet names, like this:

function copyToFirstFreeColumn() {
  const sheetRegex = /^(Tab \d)/i;
  const rangeA1 = 'A9:A12';
  SpreadsheetApp.getActive().getSheets()
    .forEach(sheet => {
      if (!sheet.getName().match(sheetRegex)) {
        return;
      }
      const currentRange = sheet.getRange(rangeA1);
      currentRange
        .offset(0, sheet.getLastColumn() - currentRange.getColumn() + 1)
        .setValues(currentRange.getValues());
    });
}

See https://regexone.com/ and MDN for more info.

3
  • This is really helpful, thanks. One tweak - I notice that this loop applies the function to any tab with "Tab" in the name. I will be adding other tabs, so is there a way to apply the function on all tabs EXCEPT certain ones (called "Targets" and "Markbook")? Apr 6 at 13:51
  • Try something like if (sheet.getName().match(/^(Targets|Markbook)$/i)). See the regex references I gave for more info. Apr 14 at 6:18
  • Sorted - thanks very much! Apr 22 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.