apps script detect new row insertion in external spreadsheet & get the values

Question

How can I detect new row insertion in spreadsheet2 through google apps script in a spreadsheet1. I want get the values of the row whenever new row is inserted in that spreadsheet2. The place where I want to get values is spreadsheet1's script.

I'm using this in my spreadsheet1 script

var ssId2 = "1Ii0w0RPcaQWKYUDhY**************************";
var ss2 = SpreadsheetApp.openById(ssId2);

Here, I cannot determine what will be the range of the new row inserted??

var values = range.getValues();
var row = values[0];
var uname = row[1];

And how can I get the values of the new row inserted whenever only change occurs i.e only when the new row gets inserted.

I've tried searching all over the internet and spent a few days but can't get it done.

You can't read event statuses of spreadsheet B from a script in spreadsheet A, because B only provides triggers to a script installed in B. You will have to modify A and B, such that B detects the new data with its trigger, and then sends that data to spreadsheet A (either directly or to a webapp written in the script for A). — tehhowch
– tehhowch, Commented Jun 12, 2018 at 16:35

edward · Accepted Answer · 2018-06-16 00:16:52Z

1

I'd not recommend you to use onEdit trigger in this case, especially if you have multiple users who can make changes concurrently, because of this bug.

"you'll have to assume that onEdit triggers are best-effort, but may not catch all edits done to the spreadsheet."

You can update sheets of spreadsheet2 with additional column and run script, which checks every 5/10 min. whether the row is empty. In case if it's not empty, assign some value to the column (Y/N) and next get values of non-empty row and set it to the spreadhsheet1,based on the status of the column.

answered Jun 16, 2018 at 0:16

edward

2433 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

apps script detect new row insertion in external spreadsheet & get the values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related