What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

    Triggering a script on Google Spreadsheet

    Asked by PrattChat on November 26, 2012 at 04:58 PM

    Hi,

    I've searched the Google developer site and Stack Overflow, and I couldn't find the answer, so please let me know if you've encountered this before.

    I wrote a simple script to send an email containing the bottom line of the spreadsheet, formatted in a particular way. I want to set the trigger on the script so that it will run every time there is a new entry, but none of the "From spreadsheet" triggers are working.

    I believe it should be On Form Submit, which has always worked with Google Forms (of course), but neither that no on Edit will work. The script runs as expected when I run it manually.

    Any advice would be welcome. For reference, here's the script:

    function emailEditors(e) {

      var url = SpreadsheetApp.getActiveSpreadsheet().getUrl();

      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

      var row = sheet.getLastRow();

      var col = sheet.getLastColumn();

     

      var date = sheet.getRange(row, 1, 1, col).getValues()[0][0];

      var name = sheet.getRange(row, 1, 1, col).getValues()[0][1];

      var contact = sheet.getRange(row, 1, 1, col).getValues()[0][2];

      var recipients = "email@email.org";

      var body = "New customer profile submitted on " + date + " by " + name + "\n" +
    "Contact: " + contact + "\n" +
    "Click here to view this profile in the spreadsheet: " + url + "\n\n" + 


      MailApp.sendEmail(recipients, "Subject Line", body, {noReply:true} );

    }

    google spreadsheet form submit
  • Profile Image
    JotForm Support

    Answered by EduardoMendez on November 26, 2012 at 05:04 PM

    Hi there, 

    May I please inquire as to what is on this e-mail and who is the recepient? and which form is to trigger this action?

    I am wondering if there is another way to accomplish this.

    I'll wait for your response.

  • Profile Image

    Answered by PrattChat on November 27, 2012 at 11:06 AM

    Sure. The email goes to a Google Group of editors for our blog. The body of the email contains the fields from the most recent form submission formatted to include HTML markup that can be copied and pasted into a new blog post in order to reduce the time spent editing the new post.

    All the steps are working, except that a new entry on the spreadsheet doesn't cause the trigger to go off. I'm wondering if anyone knows the reason, and if there is a way to make the "on Form Submit" trigger go off.

    Here is an example of the email body (continues beyond the script I pasted above):

     

    ------------------------


    New customer profile submitted on 11/23/2012 15:21:49 by Jessica
    Contact: jkeyes@prattlibrary.org
    Click here to view this profile in the spreadsheet: https://docs.google.com/a/ prattlibrary.org/spreadsheet/ ccc?key=tLCHTcZ9IFhwv2CN8bog5pg

    Copy and paste the following HTML code into the Ektron editor, using HTML view. Then switch back to the Design view to edit, add images, and add catalog links.


    <h4>Series: <a target="_blank" href="prattchat.aspx?category=Pratt%20Profiles&blogid=12298">Pratt Profile</a></h4>

    <p><strong>Your name?</strong><br />
    Jessica</p>

    <p><strong>Your age?</strong><br />
    30</p>

    <p><strong>Your local branch?</strong><br />
    Central</p>

    <p><strong>Your favorite spot in the library?</strong><br />
    My cubicle.</p>

     

    -----------------------

  • Profile Image
    JotForm Support

    Answered by idarktech on November 27, 2012 at 12:27 PM

    Hi,

    Sorry but I am a bit confused as to how you've setuped this script along with the form, do you have the form embedded somewhere? Or do you have an example page where we can test how this script works? Please let us know so we can check it here as well. Thanks!

  • Profile Image

    Answered by PrattChat on November 27, 2012 at 01:54 PM

    It's in the Google Spreadsheet. Flow:

    Jotform -> Google Spreadsheet [triggers script] -> email

    It's the script triggering I'm having trouble with. Normally, the "on Form Submit" trigger will send the email whenever a new entry is made to the spreadsheet, but for some reason the entry from the JotForm form isn't triggering it.

    If you want to test it, just set up a form with Google Spreadsheet integration, paste the short script from my first message into the spreadsheet's code editor (replace the recipient email address), and set up an "on Form Submit" trigger (or try the other triggers--none of them worked for me).

  • Profile Image
    JotForm Support

    Answered by Welvin on November 27, 2012 at 03:41 PM

    Hi,

    I am not quite sure if the function are still with us tho the integration to Google Spreadsheet is related. I admit that I am not well verse with Google Scripting.

    Have you tried searching Google itself for possible solution? CHECK THIS RESULTS

    I will try to get my colleauge attention about this and hopefully, they could help you with this custom function.

     

    Thanks

  • Profile Image

    Answered by PrattChat on November 27, 2012 at 03:44 PM

    Thanks. Yes I did search Google results and I have also posted to Stack Overflow (but no replies yet).

  • Profile Image
    JotForm Support

    Answered by Welvin on November 27, 2012 at 03:46 PM

    Hopefully someone will reply. I will also try to get my colleagues attention about this. Hopefully they could help you achieved and correct this.

    Thanks

  • Profile Image

    Answered by Sguggen on November 02, 2015 at 01:25 PM

    The answer is to go to settings in your form, change "send Post Data" to yes, and then make your function with the "doPost (e)" trigger. Pass it on.

  • Profile Image
    JotForm Support

    Answered by Welvin on November 02, 2015 at 02:28 PM

    Thank you for the suggestion @Sguggen. That is greatly appreciated.

    Thanks

  • Profile Image

    Answered by LHEDJ on June 27, 2016 at 04:28 AM

    hi @Sguggen,

    Can you tell us where you found the setting "send Post Data" in JotForms?

  • Profile Image

    Answered by LHEDJ on June 30, 2016 at 07:03 AM

    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 and will be explained in the order of the flow.

    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.