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});
}
}
}

copyTo Google Sheets Script for different workbook how targetSheet change to send data sheet to output 1 , output 2, output 3 ( with a different workbook url )

CodePudding user response：

As another approach, how about the following sample script? I believe your goal is as follows.

• You want to use both the source Spreadsheet and the target Spreadsheet.
• The source Spreadsheet is the active Spreadsheet, and the target Spreadsheet is a different Spreadsheet from the source Spreadsheet.

Modification points:

• In the current stage, when the source Spreadsheet is different from the target Spreadsheet, copyTo of Class Range cannot be used. When this is used an error like Exception: Target range and source range must be on the same spreadsheet. occurs. Please be careful about this.

• In the case of sheet.getRange("A2:D32").getValues().length > 0, even when "A2:D32" are empty, the length is always 31. Please be careful about this.

In your situation, it seems that you want to copy only the values. So, how about using getValues and setValues? When these points are reflected in your script, how about the following modification?

Modified script:

function myFunction1() {
  const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const dstSpreadsheet = SpreadsheetApp.openById("###"); // Please set your spreadsheet ID.

  const srcSheet = srcSpreadsheet.getSheetByName("January");
  const targetSheet = dstSpreadsheet.getSheetByName("Master");

  if (srcSheet && targetSheet) {
    const values = srcSheet.getRange("A2:D32").getValues();
    if (values.filter(r => r.join("")).length > 0) {
      targetSheet.getRange(targetSheet.getLastRow()   1, 3, values.length, values[0].length).setValues(values);
    }
  }
}

If you have multiple Spreadsheet IDs, how about the following sample script?

function myFunction2() {
  const spreadsheetIds = ["###", "###", "###"];// 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("Master");
    if (targetSheet) {
      targetSheet.getRange(targetSheet.getLastRow()   1, 3, values.length, values[0].length).setValues(values);
    }
  }
}

References:

• copyTo(destination, options)
• getValues()
• setValues(values)

CodePudding user response：

That's because you're handling different workbooks.

const ss = SpreadsheetApp.getActiveSpreadsheet(); will only refer to the workbook from which you run the script.

You should add a new variable for the other sheet:

const newss = SpreadsheetApp.openById("1jQ93ylP0MHzSzxH0myS9_AhTjetdvLQMXvFwg18XFEE")

I've put the ID from the link from the mastersheet, but adapt accordingly to your needs. Then you can use newss.getSheetByName("Sheet you need")

UPDATE

Possible code, if I understand you well:

function updateYTD4() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const newss = SpreadsheetApp.openById("1jQ93ylP0MHzSzxH0myS9_AhTjetdvLQMXvFwg18XFEE")
const sheet = ss.getSheetByName("January");
const targetSheet = newss.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});
}
}
}