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 "Master", in different target url google sheet

data master post

output 1

output 2

output 3

function updateYTD4() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("January");
  const targetSheet = ss.getSheetByName("Master");
  if (sheet && targetSheet) {
    if(sheet.getRange("A2:D32").getValues().length > 0){
      sheet.getRange("A2:D32").copyTo(targetSheet.getRange("C" (targetSheet.getLastRow() 1)),{contentsOnly:true});
    }
  }
}

I want master data, can post/send data to output 1, output 2, output 3, to each sheetname?

function open_by_url() {
  var urls = SpreadsheetApp.getActiveSheet().getRange("B2:B4").getValues();
  console.log(urls)
  for (var i in urls) {
    var url = urls[i][0];
    console.log(url);
    var s = SpreadsheetApp.openByUrl(url);
    console.log(s.getName());
  }
}

I try but error

function updateYTD5() {
  const urls = SpreadsheetApp.getActiveSheet().getRange("D1:F3").getValues();
  const sourceSpreadsheet = SpreadsheetApp.openByUrl(url);
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("January");
  const targetSheet = ss.getSheetByName("Master");
  if (sheet && targetSheet) {
    if(sheet.getRange("A2:D32").getValues().length > 0){
      sheet.getRange("A2:D32").copyTo(targetSheet.getRange("C" (targetSheet.getLastRow() 1)),{contentsOnly:true});
    }
  }
}

CodePudding user response：

When I saw your sample Spreadsheet, I couldn't find "Master" sheet. But, from your script, I guessed that you might copy each row of the "January" sheet to 1st tab of each Spreadsheet. If my understanding is correct, how about the following sample script?

Sample script:

In this case, please copy and paste the following script to the script editor of "data master post" spreadsheet.

function sample() {
  // Retrieve Sheet object.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("January");

  // Retrieve values from cells "A2:F".
  const values = sheet.getRange("A2:F"   sheet.getLastRow()).getValues();

  // By using the Spreadsheet URL of column "B", the values of columns "D" to "F" are appended to the 1st sheet of each Spreadsheet.
  values.forEach(([, url, , ...v]) => {
    const dstSheet = SpreadsheetApp.openByUrl(url).getSheets()[0];
    dstSheet.appendRow(v);
  });
}

• In this sample script, the values are retrieved from the "January" sheet. And, using the spreadsheet URL retrieved from the column "B", the values from columns "D" to "F" are copied to the 1st tab of Spreadsheet of each spreadsheet url.

• From your provided sample Spreadsheet, I guessed that in order to copy a row, appendRow might be useful.

References:

• forEach()
• appendRow(rowContents)

Added:

As another sample script, how about the following sample script? The result of this script is the same as the above script.

Sample script:

function sample2() {
  // Retrieve Sheet object.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("January");

  // Retrieve values from cells "A2:F".
  const values = sheet.getRange("A2:F"   sheet.getLastRow()).getValues();

  // By using the Spreadsheet URL of column "B", the values of columns "D" to "F" are appended to the 1st sheet of each Spreadsheet.
  for (let i = 0; i < values.length; i  ) {
    const dstSheet = SpreadsheetApp.openByUrl(values[i][1]).getSheets()[0];
    dstSheet.appendRow([values[i][3], values[i][4], values[i][5]]);
  }
}