Trigger an onChange event programmatically in Google Sheet Apps Script-CodePudding

I have two spreadsheets (let me call them Source and Destination). The Source spreadsheet contains the data inserted by the user, and these data are imported to the Destination spreadsheet using the '=IMPORTRANGE' function, changes in the Source will just reflect in the Destination.

In the Destination spreadsheet, there are two sheets (Sheet 1 and Sheet 2). Sheet 2 contains the imported data from the Source spreadsheet. While the last data added on Sheet 2 will be reflected on Sheet 1. You might be wondering why not just import the data in Sheet 1 instead of Sheet 2, this is because Sheet 1 contains other information (manually added by the user) that is not included in the imported data.

Here's what I did, using apps script I am able to reflect/append the data coming from Sheet 2 to Sheet 1, the only problem is the event triggering. Currently, the program will only work when I click the "Run" function in Apps Script. What I want to happen is as soon as new data is imported to Sheet 2, the event is immediately triggered to reflect the data to Sheet 1.

Here's an example of Sheet 2: Sheet 2 (Destination) contains imported data:

The second item should have been automatically added to Sheet 1 but it is not so I still need to click the "Run" function; The '=IMPORTRANGE' function is in cell A2.

Here's what Sheet 1 looks like:

Sheet 1 (Destination) reflects the data from Sheet 2

Here's my code (does not contain any algorithm for the onChange event):

const ss = SpreadsheetApp.openByUrl(url);
const ws = ss.getSheetByName("Sheet 1");

function myFunction() {
  const ss2 = SpreadsheetApp.openByUrl(url);
  const ws2 = ss2.getSheetByName("Sheet 2");
  const entryNo = ws2.getRange(ws2.getLastRow(), 1, ws2.getLastRow() - 1, 1).getValue();
  const companyNo = ws.getRange(ws.getLastRow(), 2, ws.getLastRow() - 1, 1).getValue();
  const company = ws2.getRange(ws2.getLastRow(), 2, ws2.getLastRow() - 1, 1).getValue();
  const firstName = ws2.getRange(ws2.getLastRow(), 3, ws2.getLastRow() - 1, 1).getValue();
  const surName = ws2.getRange(ws2.getLastRow(), 4, ws2.getLastRow() - 1, 1).getValue();
  const address = ws2.getRange(ws2.getLastRow(), 5, ws2.getLastRow() - 1, 1).getValue();
  const position = ws2.getRange(ws2.getLastRow(), 6, ws2.getLastRow() - 1, 1).getValue();
  const degree = ws2.getRange(ws2.getLastRow(), 7, ws2.getLastRow() - 1, 1).getValue();
  const dateHired = ws2.getRange(ws2.getLastRow(), 8, ws2.getLastRow() - 1, 1).getDisplayValue();

  const department = "";
  const schedule = "";
  const nextCompanyNo = companyNo   1;

  const dataArray = [entryNo, nextCompanyNo, company, firstName, surName, Address, 
                                      department, position, schedule, degree, dateHired ];

  ws.appendRow(dataArray);
}

I added some empty variables so I can have correct indexing of data in the array (that's what I could think of). These data will be manually edited later on.

I did try adding some code for the onChange event by assigning the property.

function propertiesStuff(){

PropertiesService.getScriptProperties().setProperty("A2",SpreadsheetApp.getActive().getActiveSheet().getRange("A2").getValue());

}

But I am getting an error which says:

Exception: The parameters (String,number[]) don't match the method signature for PropertiesService.Properties.setProperties.

How can I programmatically trigger the onChange event only when new data is added on the imported range of data? Any other changes on the spreadsheet like manual editing of data, deletion of data, adding of row, etc shall not trigger the onChange event.

CodePudding user response：

You've omitted some of your code. The propertiesStuff function you posted uses setProperty, but your error pertains to a call of setProperties.

As per the docs setProperties takes two parameters: the first is an object of key: value pairs, and the second is an optional boolean marking whether the other properties should be deleted or not.

If you're using setProperties then it should look something like this:

PropertiesService.getScriptProperties().setProperties({
  "A2": SpreadsheetApp.getActive().getActiveSheet().getRange("A2").getValue()
})

If you want to use setProperty then your line is correct, but you're running a different function which is throwing the error.