When I make the changes, the submissions are no longer sent to Google spreadsheet

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!

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!

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);

  }

 

}

 

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.)

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!

Here you go:
https://docs.google.com/spreadsheet/ccc?key=1km_80pDqOi1taqOYLi0qKZiHzKz5kOq--1GZ6rby2vI
Peter Ott
________________________________
...

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.

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

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
________________________________
...

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. 

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.

@Sguggen

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