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 plott on August 10, 2015 at 07:55 PM

    I have set up integration with Google Sheets and everything works well.  However, I want to add some custom code to the sheet (a menu, data parsing, etc).  I've also added two sheets so that I can separate data based on a value in a column.  When I make the changes to the Google Doc, my submissions are no longer captured.  If I then break the integration and re-integrate, I'm good - but without my code and extra sheets.

    How can I get around this?

    Thank you!

    after integration fails Submissions integrate custom code then
  • Profile Image
    JotForm Support

    Answered by ashwin_d on August 11, 2015 at 01:06 AM

    Hello,

    Did you make any changes in the "Submissions" sheet of the integrated google spreadsheet? Please be noted that if you make any changes in the column header of the spreadsheet, it will cause the integration to break.

    Please let us know what changes you made in the integrated spreadsheet and we will take a look.

    We will wait for your response.

    Thank you!

  • Profile Image

    Answered by plott on August 11, 2015 at 06:35 AM

    I did not make changes to the Submissions sheet.

    I added 2 new tabs - reflecting values from my lookup column.

    I then added the following code:

    function onOpen() {

      var ss = SpreadsheetApp.getActiveSpreadsheet();

      var menuEntries = [ {name: "Move Data", functionName: "moveData"} ];

      ss.addMenu("CustomTools", menuEntries);

    }

     

    function moveData() {

      // see Sheet event objects docs

      // https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events

      var ss = SpreadsheetApp.getActiveSpreadsheet();

      var s = ss.getActiveSheet();

      // var r = e.range;

       

      // to let you modify where the action and move columns are in the form responses sheet

      // var actionCol = 6;

      var nameCol = 2;

      // Get the row and column of the active cell.

      // var rowIndex = r.getRowIndex();

      // var colIndex = r.getColumnIndex();

       

      // Get the number of columns in the active sheet.

      // -1 to drop our action/status column

      var colNumber = s.getLastColumn();

      var activeRows = s.getLastRow();

      for (i = 1; i <= activeRows; i++) { 

        var sourceRange = s.getRange(i, 1, 1, colNumber);

        var targetSheet = s.getRange(i, nameCol).getValue();

        if (ss.getSheetByName(targetSheet)) {

          var targetSheet = ss.getSheetByName(targetSheet);

          var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);

          var sourceRange = s.getRange(i, 1, 1, colNumber);

          sourceRange.copyTo(targetRange);

        }

      }

      for (i = activeRows; i >= 1; i--) { 

          s.deleteRow(i);

      }

     

    }

     

  • Profile Image

    Answered by plott on August 11, 2015 at 07:09 AM

    If helpful, the form can be found at:

    http://form.jotform.us/form/52206601864148

     

    I use the Hub Name value to write to a specific tab in the sheet (Birch, Half Moon, etc.)

  • Profile Image
    JotForm Support

    Answered by ashwin_d on August 11, 2015 at 08:17 AM

    Hello plott,

    Is it possible for you to please share the integrated google spreadsheet with us? We would like to check to see what is wrong and the integration breaks.

    The following guide should help you how to share google spreadsheet:  http://computers.tutsplus.com/articles/everything-you-need-to-know-about-sharing-in-google-docs--cms-20676 

    We will wait for your response.

    Thank you!

  • Profile Image

    Answered by plott on August 11, 2015 at 08:29 AM
    Here you go:
    https://docs.google.com/spreadsheet/ccc?key=1km_80pDqOi1taqOYLi0qKZiHzKz5kOq--1GZ6rby2vI
    Peter Ott
    ________________________________
    ...
  • Profile Image
    JotForm Support

    Answered by BJoanna on August 11, 2015 at 11:50 AM

    After some investigation regarding your issue and I am afraid that google triggers will not work when new submission come to the Google spreadsheet. 

    This can be useful link where this topic is covered and you can try with proposed solution:

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

    You can also check some of other solutions.

    I will also report this to our developer to see if this can be implemented. However we can not give you any time frame when and if this will be implemented.

  • Profile Image
    JotForm Support

    Answered by BJoanna on August 11, 2015 at 11:58 AM

    I forgot to mention that also seems that submissions will not be saved in Google Spreadsheet when trigger exist inside of Google spreadsheet. 

  • Profile Image

    Answered by plott on August 11, 2015 at 01:04 PM
    I'm confused by your response.
    In the email you suggest this is not possible.
    However, the article you point me to claims it is possible.
    Could you clarify for me please?
    Peter Ott
    ________________________________
    ...
  • Profile Image
    JotForm Support

    Answered by BJoanna on August 11, 2015 at 02:24 PM

    Sorry for confusion. Regarding adding new tabs and changing columns inside of Google spreadsheet like my colleague already said please be noted that if you make any changes in the column header of the spreadsheet, it will cause the integration to break. Possibly you are not getting submissions because of that.

    And other part of problem could be triggers you added inside of spreadsheet. I am not sure if you mentioned - does those triggers are working at all?

    Article I provided are explaining how to trigger Google Apps Script when Jotform submitted and integrated to spreadsheet. However from the experience of other users I found that this is not possible and it will not work probably. Because of that I escalated issue to our developers. 

    However, please note any change inside of Google spreadsheet for now will break integration between JotForm and Google spreadsheet. 

    Let us know if you need further assistance. 

  • Profile Image

    Answered by Sguggen on November 02, 2015 at 01:23 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 BJoanna on November 02, 2015 at 02:41 PM

    @Sguggen

    Thank you for sharing this workaround with our users and with us.