I am very new to Javascript and Apps Script. I want to create a function that updates another sheet based on a date in a certain range of the active sheet. I run and no error but it doesn't transfer value from active sheet to sheet named "Cab1", in different target url google sheet

data master post

output 1

output 2

output 3

function myFunction2() {
  const spreadsheetIds = [
    "1ShPxDW9qhz4aWgaQ1G9oz7w1yh0-Wfe2VItet95UYks", 
    "13Dx3ZOpV7baSTadSApIrVVccN-bHrPlHu240Aux0fo0", 
    "14EVlqaP1ilXwopgi0ESvp_IKkSyROSF22WzWAcNAJWc"
  ];// Please set your spreadshet IDs.

  const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = srcSpreadsheet.getSheetByName("January");
  
  if (!srcSheet) return;

  const values = srcSheet.getRange("A2:D32").getValues();
  
  if (values.filter(r => r.join("")).length == 0) return;

  for (let i = 0; i < spreadsheetIds.length; i  ) {
    const dstSpreadsheet = SpreadsheetApp.openById(spreadsheetIds[i]);
    const targetSheet = dstSpreadsheet.getSheetByName("cab1");
    if (targetSheet) {
      targetSheet.getRange(targetSheet.getLastRow()   1, 3, values.length, values[0].length).setValues(values);
    }
  }
}

3 workbook link

const spreadsheetIds = ["1ShPxDW9qhz4aWgaQ1G9oz7w1yh0-Wfe2VItet95UYks", 
"13Dx3ZOpV7baSTadSApIrVVccN-bHrPlHu240Aux0fo0", 
"14EVlqaP1ilXwopgi0ESvp_IKkSyROSF22WzWAcNAJWc"];// Please set your spreadshet IDs.

but only one sheet name

const targetSheet = dstSpreadsheet.getSheetByName("Cab1");

how to order sheetname

workbook 1 namesheet cab1

workbook 2 namesheet cab2

workbook 3 namesheet cab3

workbook 3 namesheet cab4

CodePudding user response：

I believe your goal is as follows.

• You want to copy the values from "January" sheet of the source Spreadsheet to 3 Spreadsheets.
• About 3 Spreadsheets, you want to copy the values to the specification sheets.
  • "cab1" sheet in Spreadsheet A 1ShPxDW9qhz4aWgaQ1G9oz7w1yh0-Wfe2VItet95UYks.
  • "cab2" sheet in Spreadsheet A 13Dx3ZOpV7baSTadSApIrVVccN-bHrPlHu240Aux0fo0.
  • "cab3" and "cab4" sheets in Spreadsheet A 14EVlqaP1ilXwopgi0ESvp_IKkSyROSF22WzWAcNAJWc.

In this case, how about the following modification?

Modified script:

function myFunction2() {
  const spreadsheetIds = [
    { id: "1ShPxDW9qhz4aWgaQ1G9oz7w1yh0-Wfe2VItet95UYks", sheetNames: ["cab1"] },
    { id: "13Dx3ZOpV7baSTadSApIrVVccN-bHrPlHu240Aux0fo0", sheetNames: ["cab2"] },
    { id: "14EVlqaP1ilXwopgi0ESvp_IKkSyROSF22WzWAcNAJWc", sheetNames: ["cab3", "cab4"] }
  ];

  const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = srcSpreadsheet.getSheetByName("January");
  if (!srcSheet) return;
  const values = srcSheet.getRange("A2:D32").getValues();
  if (values.filter(r => r.join("")).length == 0) return;
  for (let i = 0; i < spreadsheetIds.length; i  ) {
    const dstSpreadsheet = SpreadsheetApp.openById(spreadsheetIds[i].id);
    for (let j = 0; j < spreadsheetIds[i].sheetNames.length; j  ) {
      const targetSheet = dstSpreadsheet.getSheetByName(spreadsheetIds[i].sheetNames[j]);
      if (targetSheet) {
        targetSheet.getRange(targetSheet.getLastRow()   1, 3, values.length,
          values[0].length).setValues(values);
      }
    }
  }
}

• In this modification, an object of spreadsheetIds is prepared. And, in order to manage the sheets and Spreadsheets, this object is used.