Google Sheets - Update the same target range on several sheets

user_12916676 0
Viewed 45

I would like the following code to update the same targetRange on several targetSheets: "Trip 1", "Trip 2", "Trip 3", "Trip 4", "Trip 5" etc.

But I do not know how to achieve this. I would be extremely grateful for some help.

function updateSheet() {
  let ss = SpreadsheetApp.getActive()
  let sourceValues = ss.getSheetByName("Master").getRange("A2:A").getValues().filter(String)
  let targetSheet =  ss.getSheetByName("Trip 1")
  let targetRange =  targetSheet.getRange("A4:A");
  let targetValues = targetRange.getValues().filter(String)
  let diff = targetValues.showDif(sourceValues)
  targetRange.clearContent();
  targetValues = (diff && diff.length) ? targetValues.concat(diff) : targetValues;
  targetSheet.getRange(4, 1, targetValues.length, targetValues[0].length).setValues(targetValues)
 }

Array.prototype.showDif = function (array) {
    let that = this;
    return array.filter(function (r) {
        return !that.some(function (x) {
            return r.join() === x.join();
        })
    })
}
google-apps-script google-sheets
1 Answers

In your situation, how about modifying your script as follows?

Modified script 1:

In this script, the sheet names you want to use are declared.

function updateSheet() {
  Array.prototype.showDif = function (array) {
    let that = this;
    return array.filter(function (r) {
      return !that.some(function (x) {
        return r.join() === x.join();
      })
    })
  }

  let ss = SpreadsheetApp.getActive()
  let sourceValues = ss.getSheetByName("Master").getRange("A2:A").getValues().filter(String);
  const sheetNames = [ "Trip 1", "Trip 2", "Trip 3", "Trip 4", "Trip 5",,, ];
  sheetNames.forEach(name => {
    let targetSheet = ss.getSheetByName(name);
    if (!targetSheet) return;
    let targetRange = targetSheet.getRange("A4:A");
    let targetValues = targetRange.getValues().filter(String)
    let diff = targetValues.showDif(sourceValues)
    targetRange.clearContent();
    targetValues = (diff && diff.length) ? targetValues.concat(diff) : targetValues;
    targetSheet.getRange(4, 1, targetValues.length, targetValues[0].length).setValues(targetValues)
  });
}

Modified script 2:

In this script, the sheets except for "Master" sheet are used as targetSheet.

function updateSheet() {
  Array.prototype.showDif = function (array) {
    let that = this;
    return array.filter(function (r) {
      return !that.some(function (x) {
        return r.join() === x.join();
      })
    })
  }

  let ss = SpreadsheetApp.getActive()
  let sourceValues = ss.getSheetByName("Master").getRange("A2:A").getValues().filter(String);
  ss.getSheets().filter(e => e.getSheetName() != "Master").forEach(targetSheet => {
    let targetRange = targetSheet.getRange("A4:A");
    let targetValues = targetRange.getValues().filter(String)
    let diff = targetValues.showDif(sourceValues)
    targetRange.clearContent();
    targetValues = (diff && diff.length) ? targetValues.concat(diff) : targetValues;
    targetSheet.getRange(4, 1, targetValues.length, targetValues[0].length).setValues(targetValues)
  });
}

Reference:

user_71086530
Related