*URGENT* Add external GoogleSheet

[cankue]

Hey, the following situation:

I have an AppSheet which runs on a google sheets database. Now I want to be able, to add via an external google sheet, additional rows to my already existing app sheet. So I want to run for example a Google AppScript on this specific sheet, and then the user adds a variety of rows to the existing table which is used in AppSheet. Any ideas?

The external google sheet will have/already has manually implemented data validation via the gsheet functions.

[Rafael_ANEIC-PY · Accepted solution]

Hello @cankue, here's the script that I use for doing exactly that:

function smartExport() {
  var ss=SpreadsheetApp.openById('Source spreadsheet ID');
  var sh=ss.getSheetByName('Source sheet name'); 
  var srg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  var dA=srg.getValues();

  if(dA.length>0){
  var tss=SpreadsheetApp.openById('Target spreadsheet ID');
  var tsh=tss.getSheetByName('Target sheet name'); 

  tsh.getRange(tsh.getLastRow()+1,1,dA.length,dA[0].length).setValues(dA);
  SpreadsheetApp.flush();
  
};
}

 However, this script won't check if the rows where already sent, so for using this, you should erase the rows after sending them, you can do this manually or modifying the script, you could also add a separate column for saving which rows have been sent, so that you don't have to delete anything at all.

If this works for you let me know hehe.

[cankue]

Hey it works perfect! Thank you very much!