OnChange script not working when data is sent to sheet instead of manually entered

Question

I have a sheet (spreadsheet b) that data gets sent to from a source sheet (spreadsheet a) using a submit button generated by a script.

Spreadsheet b uses this code to then copy this data automatically to another spreadsheet (spreadsheet c):

function onChange() {

var sourceSpreadsheet = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Albany");
var sourceData = sourceSpreadsheet.getRange("A1:E39").getValues();
var targetSpreadsheet = SpreadsheetApp.openById("1NRhJ-
g6s8i05XVsAZDepJlWTB1zLuiKwS7zxp66QWJE").getSheetByName("Albany");

targetSpreadsheet.getRange("A1:E39").setValues(sourceData);
}

This code works if a physical edit has been made to spreadsheet b, but does not work when the data has been sent to spreadsheet b from spreadsheet a using the submit button. How can I get this to work?

(Note: I can't seem to have spreadsheet a go straight to spreadsheet c because the editors using spreadsheet a are not shared with spreadsheet c.)

I can't use the importrange function because edits might have to be made to the data on spreadsheet c.

Cooper Cooper · Accepted Answer · 2017-10-23T21:07:16

These are the only types of changes detected by onChange: EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT Link

Perhaps you can wrap the current actions into the original function run by the submit button.

OnChange script not working when data is sent to sheet instead of manually entered

2 Answers

Short answer

Explanation