Question

How to automatically run code from Google Sheets script?

  • 27 July 2021
  • 3 replies
  • 59 views

Hi there ! 

I am trying to run a script (in javascript) automatically. The trigger in the google app script do not work but when I run my code manually it is correct. 

Here the code : 

function deleteRows() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = ss.getSheetByName('Feuille 1');

var r = s.getRange('A:A');

var v = r.getValues();

for(var i=v.length-1;i>=0;i--)

if(v[0,i]=='a')

s.deleteRow(i+1);

};

I have tried to use the “code by Zapier” action bu it does not work either. Here the error message I get : 

Error: You did not define `output`! Try `output = {id: 1, hello: "world"};`

Does someone know if it is possible to code a google sheet code in the action code by zapier ? And why does it not work ? 

 

Thank you all for your time :) 


3 replies

Userlevel 7
Badge +10

HI @Mathiilde 

This has been discussed here before: 

 

You could send a webhook to the script to trigger the run. Though I am not sure getActiveSpreadsheet will work since this is not running while the spreadsheet is open. You may need getSpreadsheetById instead. 

Hi @GetUWired  and thank you a lot for your answer. 

I went through the conversation you sent me and that helped me. 

However, I do not understand the doPost() part, do you have any idea or precision about where I need to put it in my code ? 

Here is my code : 

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Feuille 1');
  var r = s.getRange('A:A');
  var v = r.getValues();
  for(var i=v.length-1;i>=0;i--)
    if(v[0,i]=='a')
      s.deleteRow(i+1);
};

Finally I do not understand why getActiveSpreadsheet will not work I am a kind of beginner in coding. 

Userlevel 7
Badge +10

the doPost function is how you would receive the webhook from Zapier. Essentially, when you post to the script url, doPost is the function that runs.

the doPost function could possibly something that just calls the deleteRows function. 

function doPost( e ) {

  deleteRows()

}

More in depth explanation can be found here: https://www.labnol.org/code/19871-get-post-requests-google-script 

 

i am not sure getActiveSpreadsheet will work because it returns the currently active sheet. If the script is bound to a sheet then I think it would work. 

If not, there are other methods for defining a sheet like openById or openByUrl.  

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyidid

 

Reply