- L1HAsked on September 18, 2014 at 09:31 AM
we have created a form for user-submitted details. We would like to use the Google Spreadsheet submissions spreadsheet to add input from the team (e.g. comments, prioritization etc.) - which should be several more columns at the end of the spreadsheet.
I have noticed that with new submissions - or after a certain time, one of the two - the additional columns are deleted. I guess the spreadsheet updates itself from the available form fields.
My first workaround was to create a second spreadsheet with formula links to the original submissions spreadsheet. This sort of works (don't know if it will be deleted once the spreadsheet updates) but instead of filling the existing lines for each new submission, the submissions spreadsheet appears to insert new lines.
Hence, the formula links of the second spreadsheet are 'broken':
In the original sheet, Cell B10 is a data point for one submission, B11 is the same data point for the next submission. I duplicate all data with the formula ='OriginalSubmissions'!B10 . I replicate this so that B11 in the duplicated sheet is ='OriginalSubmissions'!B11.
Once a new submission comes in, a new line is inserted - so every time a new submission comes in, I have to update the formula links (because if I don't, the duplicated sheet jumps from ='OriginalSubmissions'!B10 to ='OriginalSubmissions'!B12 in the next line.
Can you help me? Am I doing it wrong? Is there another way to make annotations to submissions and keep them?
- L1HAnswered on September 18, 2014 at 10:41 AM
A quick update:
The second sheet in the submissions document was deleted, probably after the last new submission came in. The workaround is not stable. Linking to a different document is not possible in Google Spreadsheets.
What would you recommend me to do?
- JotForm SupportdavidAnswered on September 18, 2014 at 12:29 PM
As far as I know you should be able to update your spreadsheet as long as you do not alter the fields that are associated with your JotForm. The key here is the column names use in the spreadsheet. As long as you do not change the column name created on the spreadsheet when the form was integrated, all succeeding submissions will still map the appropriate fields even if you move the column number (by adding/inserting new columns).
Here is a screen of my test, wherein I appended the new column "Issue Resolved" right before the 1st original column (Submission Date). As you can see I was able to submit the last data (3rd row).
It should retain the added columns in the sheet as long as the form itself is not changed. If the form itself is changed the sheet will update accordingly and potentially delete added columns and data.
- L1HAnswered on September 18, 2014 at 12:49 PM
Thank you very much!
This helps a lot. I will try to have the final names for questions and columns asap so there are no further changes.
- JotForm SupportdavidAnswered on September 18, 2014 at 01:25 PM
You are very welcome! Let us know if you have any further questions and we will be happy to help.
- iStoreAnswered on December 12, 2016 at 12:04 AM
I have tried adding columns and it breaks my integration every time. I have also tried just rearranging the columns and then it places data from jotform in the wrong column. In the screen shot example below I swapped column A and column B, and you will see that jotform misplace 'Submission Date' into 'Signature Image'. Is this a bug in the integration or am I doing something wrong?
Sorry I cannot add the screenshot because your 'insert image' still doesn't work. (I'm using current version of safari and this didn't work a couple years ago when I tried it)
- JotForm SupportsethAnswered on December 12, 2016 at 01:44 AM
I have tested the issue and I could replicate that when you change columns in the excel sheet after integration, the integration doesn't look up to the column names but it inserts according to the column sequence with respect to the place of the row in the form. However, the integration doesn't broke on my end still working:
On the other hand when you add new columns before, after or between the columns that were created automatically by the integration, as you said, the data is placed in the wrong columns. The only case that the integration works properly is when you add a column at the end of the sheet where you did not change any other columns.
I want to state that this integration is updated with respect to the new JotForm builder version 4. I am escalating this issue as a feature request. Our developers will work on this and when they fix it, they will notify you via this thread.
Thank you very much for your concern.