Trigger Google Apps Script when Jotform submitted and integrated to spreadsheet

Question

Is there a way to trigger a Google Apps Script when a Jotform is integrated and submitted ??? I guess even a way to trigger of the new row created.

This is the flow i'm looking for.

Jotform submit >> Google Spreadsheet >> Trigger Google Apps Script >> do some stuff

Cheers...

Answer 1

Yes you can,

After you added a script to your spreadsheet using script manager, enable onChange event trigger as follows:

First choose Resources->Current project's triggers...

Then set the trigger as:

You can do anything you want inside the onChange function. Here is an example that sends newly inserted row as an email:

function onChange(e){

    // sends newly inserted row as an email
    if (e.changeType == "OTHER") { // mean the sheet is updated via an api call

    var sheet = SpreadsheetApp.getActiveSheet();

    var rows = sheet.getLastRow();
    var columns = sheet.getLastColumn();

    var data = [];

    for (var i=1; i<columns; i++){
      data.push(sheet.getRange(rows, i).getValue());
    }

    MailApp.sendEmail("[email protected]", "autoTriggeredByJotform", Utilities.jsonStringify(data));
  }
};

Answer 2

I am not sure if it is possible to trigger a google apps script using google spreadsheet. I guess there should be a way.

https://developers.google.com/apps-script/understanding_events

But, it is possible to trigger an external URL/script when a JotForm form is submitted:

http://www.jotform.com/help/51-How-to-Post-Submission-Data-to-Thank-You-Page

Answer 3

If you'd like to use JotForm, Formstack, Zapier, etc. or any other Form, other than Google Forms to Trigger an Ultradox, you'll struggle as I have for the last 2 days putting together a proper solution to this question that was first asked in Nov 2012.

This solution will be tailored for a JotForm to Ultradox Trigger.

From the New Ultradox > Name it 'Trigger' > Set a 1 minute Timer as the first block (otherwise the trigger will start before JotForm has a chance to write the new form data to your Sheet) > Insert a Google Apps Script - My Script Block > Click Create and Rename to "Trigger" > Replace lines 1 to 78 with the code below:

// The following method will be invoked whenever Ultradox executes the script
function execute(mode1) {
  var sheet = SpreadsheetApp.openById('1KFVcrrCpiPKn7pOz2sQoI5IPjiPNlNKuHpykhlKA0fY').getSheetByName('Submissions');
//Change the sheet Id and name as appropriate
   var lastRow = sheet.getLastRow();
   var response = UrlFetchApp.fetch('http://www.ultradox.com/batch?id=x5OPzk9iQ5LssjaeEgxScBMqXA3Pnc&chunk='+lastRow);}

From the Script editor, On line 4, change the Sheet ID (to your response sheets ID, found in its URL) and the name of the Sheet tab inside the Spreadsheet the responses are on (not the name of the Spreadsheet) > go to the Ultradox you want Triggered > Click File > Integrate > copy your Integration URL > Back to the Script, Replace the URL on line 9, "&chunk=" is part of the script and needs to stay at the end of your URL.

Click Publish > Deploy as web app > change Who has access to the app: to Anyone > Deploy > Copy the Current web app URL.

Return to the Trigger Ultradox and click the Gear icon on the Script Block (if unavailable, delete this block, add another Script Block, click Select, find and associate the script you created with this Block) paste the 'app URL' to 'Enter published script URL > OK > Click File > Integrate > copy your Integration URL.

Go to JotForm and Open the custom 'Thank You' page > Insert/edit image > paste the Integration Link from the Trigger Ultradox in the Source > OK > Resize the new box to something very small. Be aware that as this point every time you open the Thank You page your Ultradox will be Triggered :-)

Summary of flow... JotForm is submitted > Thank You page Triggers the Trigger Ultradox 1 min timer > Form data is written to sheet > The Trigger Script gets the last row number from your sheet > The script appends this row number as a 'Chunk' to the Ultradox Integration URL before Triggering the integration.

Would you like to place the Trigger script inside the Ultradox you'd like Triggered, rather than having it as a separate Ultradox?

You'll have to work out how to pass the "lastRow" variable from the Trigger Script to the "Pick row(s)" building block. As this would sit above your work flow, you'll also have to work out an "Execute only if the following condition is true" for the Trigger Script, so you may trigger other rows without the script interfering.