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

    Trigger Google Script with form submission

    Asked by cblundell on December 04, 2014 at 11:41 AM

    I'm trying to replace an existing google form with jotform. Currently, a submission to the google form triggers a script which posts the responses to cartodb as demo'd in this blog post. I'd like the jotform submissions to do the same but I can't work out how. I'm not the first to have this issue - see this question which was never resolved. These questions remain: 

    1.  Can a trigger be created (in Google spreadsheets, scripts) that runs when JotForm data is submitted.

    2.  Or is it possible for JotForm to send whatever is needed for the spreadsheet to see an "onform submit" like the google form does?

    I had expected to be able to manually change the trigger to run "OnEdit" or "OnChange" rather than when a form is submitted but that doesn't seem to be working and I can't figure out why not. If I can't work this out I'll have to go back to google forms - please help! 

    Thanks!
    Caitlin

  • Profile Image
    JotForm Support

    Answered by david on December 04, 2014 at 12:33 PM

    Hi,

    I did a bit of digging and I believe I found a solution.  Here is a discussion I believe is about exactly what you are looking for, including the script you would need in order to make it work:

    http://stackoverflow.com/questions/16183127/trigger-google-apps-script-when-jotform-submitted-and-integrated-to-spreadsheet

    It appears to work with the "OnChange" function in Google spreadsheets as you had initially intended.

  • Profile Image

    Answered by GVUgraphics on February 04, 2015 at 09:14 AM

    The solution suggested above doesn't work

  • Profile Image

    Answered by Sean on February 04, 2015 at 11:12 AM

    Hello GVUgraphics,

    Thank you for the update. Since the solution suggested by my colleague @David didn't work for you. I will be doing some checks of my own to see if I can come up with a solution for this. Hopefully by the end of the day I should have a solution. I will be in touch with you.


    Cheers!
    Sean

  • Profile Image

    Answered by GVUgraphics on February 04, 2015 at 04:28 PM

    Thanks a lot for your reply. To narrow this problem one more time the workflow is the following: data from Jot web form goes to spreadsheet (this works fine) => new submissions activate the trigger which run a custom scrip bound to the spreadsheet.

    And here is the problem.

    The script itself works fine and if a onEdit or onChange trigger installed it fires when you manually make a change in a spreadsheet. But nothing happens when a new submission arrives and of course this submission make a massive change in a spreadsheet. Adding onFormSubmit trigger doesn't work neither. It looks like a trigger doesn't consider Jot form submission as an event at all. With Google forms everything works just fine. It seems to me Jot form submission should pass some additional info to the spreadsheet to be detected as a form submission event.

    Adding onChange or onEdit functions in a script doesn't help.

     

    Thanks a lot in advance

  • Profile Image

    Answered by Sean on February 04, 2015 at 09:08 PM

    Hello GVUgraphics,

    I transferred your post to a new thread where it will be addressed in details. We recommend that each individual's thread is being addressed separately so that we are better able to assist. I will post an update on your issue soon.


    Cheers!
    Sean

  • Profile Image

    Answered by 10ninety on May 26, 2015 at 01:14 AM

    I have this same problem ...however I had it working last year and now it's broken....did we find a solution to this?
  • Profile Image
    JotForm Support

    Answered by Chriistian on May 26, 2015 at 03:05 AM

    Hi 10ninety, 

    Thanks for contacting us.

     

    I have moved your question to a separate thread since this is an old thread. We will attend to your question as soon as possible.

     

    Regards.

  • Profile Image

    Answered by Sguggen on November 02, 2015 at 01:24 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

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

    Can't find "Send Post Data", go to: http://www.jotform.com/help/51-How-to-Post-Submission-Data-to-Thank-You-Page

  • Profile Image

    Answered by DeCiccoMeat on October 21, 2016 at 12:11 PM

    @Sguggen

     

    I turned on Send Post-Data and I used doPost(e). It still won't trigger my script when I form Submit. Do you have any other suggestions?

  • Profile Image
    JotForm Support

    Answered by david on October 21, 2016 at 12:26 PM

    Unfortunately, this behavior is defined by Google's policies and external changes cannot trigger Google app scripts.

    However, there is way to get over it by using a time-based trigger and saving the last edit date on a different document. It is not actually too difficult as fully described here:

    http://stackoverflow.com/a/28240569/2378247

    So rather than triggering on submit, it would trigger based on last edit to the sheet.

  • Profile Image

    Answered by DeCiccoMeat on October 21, 2016 at 12:40 PM

    Thanks for the quick response. Questions:

     

    The document file is the storage location for the date (and time) of the last update to the spreadsheet.

    Is this something I need to define in the code? I inserted my sheets ID, but it still didn't update after the time defined. I feel like I'm missing something,

  • Profile Image
    JotForm Support

    Answered by david on October 21, 2016 at 01:38 PM

    I believe it is the spreadsheet key that is used for the sheet ID:

    http://www.coolheadtech.com/blog/use-data-from-other-google-spreadsheets

  • Profile Image

    Answered by DeCiccoMeat on October 24, 2016 at 10:33 AM

    I'm having a difficult time getting this to work. Please advise:

    When I try and run the script I am getting the error "Document is missing (perhaps it was deleted?)"

    I want for my google sheet to auto-sort when a user submits, or if that's not possible, once a minute is fine. 

    Here's the script I want to run - It works fine on its own, but when I try and set i up with the timer link that you sent I get the error listed above:

     

     

     /**

     * Automatically sorts the 1st column (not the header row) Ascending.

     */

    function onEdit(event){

      var sheet = event.source.getActiveSheet();

      var editedCell = sheet.getActiveCell();

     

      var columnToSortBy = 1;

      var tableRange = "A2:T99"; // What to sort.

     

      if(editedCell.getColumn() == columnToSortBy){   

        var range = sheet.getRange(tableRange);

        range.sort( { column : columnToSortBy, ascending: true } );

      }

    }

  • Profile Image
    JotForm Support

    Answered by david on October 24, 2016 at 11:38 AM

    I do not see any part of the script that is actually grabbing the sheet.  This is the part that gets the correct sheet:

     var doc = DocumentApp.openById('the File ID');

    The file ID needs to be the sheet ID:

    http://www.coolheadtech.com/blog/use-data-from-other-google-spreadsheets

    Your script grabs the active sheet, however, if there is no sheet selected, you will receive the document is missing error.

    Keep in mind, the sheet integrated with your form will not be editable.  Each time a new submission is received, the data will be arranged in the format it is exported from JotFrom.  Data from the integrated sheet would need to be copied to a new sheet if you wish to manipulate it.

     

  • Profile Image

    Answered by DeCiccoMeat on October 24, 2016 at 11:46 AM

    Here is the code that is calling for the sheet. The code I posted from above is what I want to happen every minute. I wanted it to call the script on Form Submit but I understand that Third-Party Apss (JotForm) cannot call triggers on google sheets, so I was looking for a workaround (time-duration triggering, versus on FormSubmit)

     

    function monitorSheetFile() {

      // Open a document by ID.

      var doc = DocumentApp.openById("1U7advEIZ-XbMAwDvlMyS_PkR1vNqPlIhnLj_xeSxZUU");

     

      var docBody = doc.getBody();

      var recordedDateModified = docBody.getText(); //Get text out of the Doc file

      Logger.log('theLastDateModified: ' + recordedDateModified);

     

      //Get the date of the last modification of the spreadsheet

      var ssFile = DriveApp.getFileById('1U7advEIZ-XbMAwDvlMyS_PkR1vNqPlIhnLj_xeSxZUU');

      var whenWasSS_Changed = ssFile.getLastUpdated();

     

      Logger.log("whenWasSS_Changed: " + whenWasSS_Changed);

     

      if (recordedDateModified != whenWasSS_Changed) {

        //Record the new date in the Doc file

        docBody.clear();

        docBody.setText(whenWasSS_Changed);

     

       var sheet = event.source.getActiveSheet();

      

      var editedCell = sheet.getActiveCell();

     

      var columnToSortBy = 1;

      var tableRange = "A2:T99"; // What to sort.

     

     if(editedCell.getColumn() == columnToSortBy){   

        var range = sheet.getRange(tableRange);

        range.sort( { column : columnToSortBy, ascending: true } );

      }

    }

    }

  • Profile Image
    JotForm Support

    Answered by david on October 24, 2016 at 12:59 PM

    Since this thread is getting longer and including document ID's and such, I have moved it to a thread of its own.  I will address your recent post in the following thread shortly:

    https://www.jotform.com/answers/969878