CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
dra
dra

Reputation: 45

copy.to send data, but it's a different Google Drive email

I'm very new to Javascript and Apps Script I'm looking to create a function that updates another sheet based on the date in a specific range of the active sheet. I run and there are no errors but it is not transferring the values from the active sheet to the named sheet "January".

Please help me see what I'm not seeing.

function myFunction2() {
const spreadsheetIds = ["https://docs.google.com/spreadsheets/d/1FpZZJrYrVqHLSCp3i5vY0qGWPOFD7lsUKu1DIa52C18/edit#gid=0"];// 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("Cab5");
if (targetSheet) {
targetSheet.getRange(targetSheet.getLastRow() + 1, 3, values.length, 
values[0].length).setValues(values);
}
}
}

Master data

output 1 user 2

I want to send data, but it's a different Google Drive email

Master data [email protected] send data to output 1 user 2 [email protected]

Upvotes: 0

Views: 22

Answers (1)

Tanaike
Tanaike

Reputation: 201603

Modification points:

  • In your script, spreadsheetIds has the URL. By this, I think that an error occurs at SpreadsheetApp.openById(spreadsheetIds[i]);. But, you say I run and there are no errors. So, I'm worried that you might have miscopied your script.

  • From transferring the values from the active sheet to the named sheet "January", in this case, I think that const srcSheet = srcSpreadsheet.getSheetByName("January"); is const srcSheet = srcSpreadsheet.getActiveSheet();. And, const targetSheet = dstSpreadsheet.getSheetByName("Cab5"); is const targetSheet = dstSpreadsheet.getSheetByName("January");.

  • About copy.to send data, but it's a different Google Drive email, in this case, please confirm that you have permission for putting the values to the Spreadsheet of another user account. If you don't have it, an error occurs at const dstSpreadsheet = SpreadsheetApp.openById(spreadsheetIds[i]);. Please be careful about this. Before you test the script, please confirm whether the destination Spreadsheet has already been shared with your account as the writer.

When these points are reflected in your script, it becomes as follows.

Modified script:

function myFunction2() {
  const spreadsheetIds = ["1FpZZJrYrVqHLSCp3i5vY0qGWPOFD7lsUKu1DIa52C18"]; // Modified

  const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = srcSpreadsheet.getActiveSheet(); // Modified
  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("January"); // Modified
    if (targetSheet) {
      targetSheet.getRange(targetSheet.getLastRow() + 1, 3, values.length,
        values[0].length).setValues(values);
    }
  }
}

Upvotes: 2

Related Questions