Solved : - Google Script and Appsheet integration

App_DM · 02-27-2020 10:12 AM

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.

Only onChange trigger works.
Avoid having any alert messages within the scripts.
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.
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

MultiTech

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

App_DM

Thank you. I am glad it helps.

Samudra

Marc_Dillon

Just to link more info:

5starbiz

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

5starbiz

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…
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

LeventK

@5starbiz

You need to set an onChange() trigger to make it work
You need to add the event object as a parameter to your function
You cannot use system parameters as variables; var source = e.source is not a valid statement
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
}

farmertom

Hello - I am sorry I dont understand. I am running into the same issue but I am unsure which lines to change. I have a database in google sheets and I update it both in Appsheet and Google Sheets manually. But when I use the Appsheet, the changelog (made with scripts) does not function. I have the following script:

function onEdit(e) {  
  var changelogSheetName = "Changelog";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = SpreadsheetApp.getActiveRange();
  var timestamp = new Date();
  var currentSheet = ss.getActiveSheet();
  var currentSheetName = currentSheet.getName();
  var previousValue = e.oldValue;
  var newValue = cell.getValue();
  var typeChange = "Edit";
  var field = currentSheet.getRange(1, cell.getColumn()).getValue();
  var id = currentSheet.getRange(cell.getRow(), 1).getValue();
  var id2 = currentSheet.getRange(cell.getRow(), 2).getValue();

  // if it is the changelog sheet that is being edited, do not record the change to avoid recursion
  if (currentSheetName == changelogSheetName) return;
  var changelogSheet = ss.getSheetByName(changelogSheetName);
  
  if (changelogSheet == null) {
   
    // no changelog sheet found, create it as the last sheet in the spreadsheet
    changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());
    
    Utilities.sleep(2000); // give time for the new sheet to render before going back
    
    ss.setActiveSheet(currentSheet);    
    changelogSheet.getRange('A1:I1').setBackground('#E0E0E0');
    changelogSheet.appendRow(["Timestamp", "field", "id", "id2", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"]);
    changelogSheet.deleteColumns(8,19);
    changelogSheet.setFrozenRows(1);
    changelogSheet.setColumnWidth(1, 170);
    changelogSheet.setColumnWidth(7, 170); 
    changelogSheet.protect();
  }
  
 
  var user = Session.getEffectiveUser().getEmail();
  
  if (previousValue == null){
    typeChange = "Add"; 
  } else if (newValue == "") {
    typeChange = "Remove";  
  } 
  
  changelogSheet.appendRow([timestamp, field, id, id2, currentSheetName, cell.getA1Notation(), typeChange, previousValue, newValue, user]);
}