How to trigger Google Apps script function based on insert row via api

Question

I have a Google Sheet with 5 columns (First Name, Address, SKU, Quote, Status).
I have an apps script function (createQuote) which looks at the above variable's values from google sheet row and create a google document quote replacing the variables to values.
I use Zapier to insert row into my above google sheet.

What am struggling with-: I need a way to trigger my createQuote function right when a new row is inserted via zapier (Google Sheet API call).

I tried playing with triggers but couldn't make it, any help is appreciated.

thank you

here is the code for my function-

function quoteCreator(){

docTemplate = "googledocidgoeshere"
docName = "Proposal"

  var sheet = SpreadsheetApp.getActive().getSheetByName("Main")
  var values = sheet.getDataRange().getValues()
  var full_name = values[1][0]

  var copyId   = DriveApp.getFileById(docTemplate).makeCopy(docName+" for "+full_name).getId()


  // Open the temporary document
 var copyDoc  = DocumentApp.openById(copyId);
// Get the document’s body section
 var copyBody = copyDoc.getActiveSection();
// Replace place holder keys/tags,  
 copyBody.replaceText("keyFullName", full_name);
 copyDoc.saveAndClose();
// Convert temporary document to PDF by using the getAs blob conversion
  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");

// put the link of created quote in the quote column
  var url = DocumentApp.openById(copyId).getUrl()
 var last = sheet.getRange(2, 7, 1, 1).setValue(url)


 }

Note-: I haven't put the loop yet in above, i'll do that once it starts working as per my requirements.

@Brian - thank you, just updated the question with my function. — Gela Ram
– Gela Ram, Commented Nov 2, 2017 at 14:11

score 7 · Accepted Answer · 2017-11-02 17:06:38Z

Changes made via Sheets API or Apps Script do not fire onEdit triggers. I give two workarounds for this.

Web app

Have whatever process updates the sheet also send a GET or POST request to your script, deployed as a web application. As an example, a GET version might access https://script.google.com/.../exec?run=quoteCreator

function doGet(e) {
  if (e.parameter.run == "quoteCreator") {
    quoteCreator();
    return ContentService.createTextOutput("Quote updated"); 
  }
  else {
    return ContentService.createTextOutput("Unrecognized command");
  } 
}

The web application should be published in a way that makes it possible for your other process to do the above; usually this means "everyone, even anonymous". If security is an issue, adding a token parameter may help, e.g., the URL would have &token=myToken where myToken is a string that the webapp will check using e.parameter.token.

GET method is used for illustration here, you may find that POST makes more sense for this operation.

Important: when execution is triggered by a GET or POST request, the methods getActive... are not available. You'll need to open any spreadsheets you need using their Id or URL (see openById, openByUrl).

Timed trigger

Have a function running on time intervals (say, every 5 minutes) that checks the number of rows in the sheet and fires quoteCreator if needed. The function checkNewRows stores the number of nonempty rows in Script Properties, so changes can be detected.

function checkNewRows() {
  var sp = PropertiesService.getScriptProperties();
  var oldRows = sp.getProperty("rows") || 0; 
  var newRows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main").getLastRow();
  if (newRows > oldRows) {
    sp.setProperty("rows", newRows);
    quoteCreator();
  } 
}

@GelaRam If this answer helped then you should at least up vote it. You now have a new question, which should be asked in a separate post. Include the code from both the POST request and the Apps Script code.

Collectives™ on Stack Overflow

How to trigger Google Apps script function based on insert row via api

1 Answer 1

Web app

Timed trigger

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Web app

Timed trigger

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related