Solved : - Google Script and Appsheet integration

Hi friends,

recently I was stuck with integrating Google Script (operating on my spreadsheet) and AppSheet. I read many posts and tricks over the internet and saw many people facing problems with it. I finally got it working with the following tricks. These are all available in various forums over the internet, but a summary might help.

  1. Only onChange trigger works.

  2. Avoid having any alert messages within the scripts.

  3. Use
    var ss = SpreadsheetApp.openById(“1wdxU42mANDqOw0UJkhkCdq73nRjK82HtB8kVrFpIPZw”)Instead of . getActiveSheet() kind of usage. This is because when Appsheet is updating data, the spreadsheet is not open and therefore getActiveSheet() will not work.

  4. the event object within the onChange() method does not directly give the range selected/updated. But you can get it using
    var source = e.source;
    source.getActiveRange();

5.Finally, you need to add this trigger via Edit->Current Project Triggers option. Make sure you select the correct function to run, source as Spreadsheet and event type as OnChange.

Hope this helps.

Samudra

10 Likes

This is wonderful, thank you for all this. This is the stuff that matters when you’re working on this integration.

Just to link more info:

4 Likes

Thank you. I am glad it helps.

Samudra

Thank You so Much for this! Just what I was looking for. For your Answer, #4-the event object within the onChange() method does not directly give the range selected/updated. But you can get it using
var source = e.source;
source.getActiveRange();

Where do I enter the var source script? I entered in the other script under myfunction, but am confused on where to add the var source script.
Thanks for your reply!

G

I think I figured out where to put the code. I have it like this…
function myFunction() {
var ss = SpreadsheetApp.openById(“15D9Q7292xRY6I06Igrq3rqRoaR3TwT83PqtFF3Gytww”)
var source = e.source;
source.getActiveRange();
}

Is there anything that I’m missing? When I run the code, it gives me an error of ReferenceError: e is not defined (line 3, file “Code”)
Is there something else that I’m supposed to be adding? I’m a little new to programming…:slight_smile:
The column that I want this to run on is Column B of this attached screenshot. Its the Manage Order Tab on the bottom of that sheet…
Thanks

@5starbiz

  1. You need to set an onChange() trigger to make it work
  2. You need to add the event object as a parameter to your function
  3. You cannot use system parameters as variables; var source = e.source is not a valid statement
  4. Event object always refers to the active spreadsheet, therefore below declarations are the same:
var ss = SpreadsheetApp.openById(".....")
var source = e.source // though this statement is already invalid
function myFunction(e) {
	var ss = SpreadsheetApp.openById(“15D9Q7292xRY6I06Igrq3rqRoaR3TwT83PqtFF3Gytww”)
	var source = e.source;
	source.getActiveRange();
}

You can wrap this function like this, which is more correct:

function myFunction(e) {
	var ss = e.source; // returns the active spreadsheet as 'event object'
	var sht = ss.getActiveSheet(); // assigns active sheet of active spreadsheet
	var rng = sht.getActiveRange(); // assigns active range in active sheet of active spreadsheet
}
1 Like