Saving submissions in Google Spreadsheet removes all new columns and sheets

Hi,

After a few tries on troubleshooting, I've decided to formally send a support ticket for this issue.

One of our team's Google Sheets, integrated with one of our forms, keeps on 'resetting' to the default, unformatted style (similar to a freshly made Sheet file) every time a new submission is sent. I am fully aware that my case is not isolated, and has been brought up from a few users:

http://www.jotform.com/answers/440523-Google-spreadsheet-reset-formatting-every-time

http://www.jotform.com/answers/492409-Google-Spreadsheet-is-being-reset-to-default-format-upon-a-form-update

http://www.jotform.com/answers/452563-Colors-and-Font-formatting-reset-when-new-submissions-received-on-integrated-Google-spreadsheet

Commonly, we share the same requirement; we needed the sheet to be formatted in some way or another for keeping tabs and monitoring the status of each row/line. With my team, we usually insert new columns to further customize the way we keep our data. I'm confident to do so since, as far as I know, new data/rows are appended so long as the column headers match with Jotform's fields. This is not the case, however, with the concerned Sheet since it keeps on 'resetting' regardless when a new column inserted or cells are formatted. Apart from the said Sheet, all of our integrated Google Sheets are working and updating without ruining the format or deleting columns of data we have created.

I understand that this may have been part of a background update for Google Integration (not intentionally a bug or error), however, this thread suggests to retain how the old integration works (not resetting formats). The functionality was tremendously helpful, removing how it previously worked did not create improvements (at least on this aspect). Here's hoping there's a chance with this.

Most of our files are confidential, and I will not provide any links of any sort. This thread is kept public for everyone to share the same experience. Thanks!

UPDATE: Even adding new sheets to avoid editing the integrated sheet proves to be an impossible solution. The whole sheet updates and removes ALL other sheets. It is also impossible to link the data to a separate spreadsheet since the IMPORTRANGE function in Google Sheets no longer works properly. I'm no longer willing to explore alternative solutions on this. The suggestion is the only appropriate fix for this.

I would just like to confirm if what I understood is correct.

You would like to:

1. add new columns to spreadsheet and keep them even after a submission is created
2. add new sheets to the spreadsheet and not have them deleted when entry of a new submission is made.

Is this correct?

Yes, those correct. However, I believe this is not entirely Google Sheet's issue, but with how Jotform appends new entries to the integrated sheet. Previously, on how we observed it, Jotform would simply append new data from newly sent submissions to its corresponding header and column, e.g. even if a header/column is missing, if columns are arranged differently than how they appear from Jotform, or if there are more columns (it'll be left as blank since there are no corresponding field from Jotform).

even if a header/column is missing, if columns are arranged differently than how they appear from Jotform, or if there are more columns (it'll be left as blank since there are no corresponding field from Jotform).

I just want to inform you also that if you manually alter/customize the integrated spreadsheet (change column name, add/remove column) -- it will break the integration .

This means that new submissions from jotform will no longer append to the google spreadsheet.

We recommend that as much as possible to not alter/customize the integrated spreadsheet itself to prevent the issue of failed integration.

If you need a customize spreadsheet for the submission data, you can use the Import Range function of the spreadsheet and use a separate sheet where all the same data were imported. The original Submission spreadsheet can serve as the landing sheet for the data.

Hope this help. Let us know if there is more we can assist you with.

Thanks.

Well then I believe one of our integrated spreadsheets has an unconventional convenience, since no matter how many times it has been customized (as long as the column/headers are kept), the integration remains and all new submissions are appended correctly to the correct columns.

Integration isn't the issue here. The concerned spreadsheet is integrated, no data has been missing out, but unlike our other spreadsheets that functions perfectly well, it kept on resetting the whole spreadsheet. If this is how the way it works, then I would suggest to make it so that the data will be appended to any available/proper headers. Again, this is not an isolated case. I believe the idea of integrating the forms to Google Spreadsheet is to have a more refined control on data monitoring at the best convenience (not needing to manually move data, not needing to resort to any other functions or method).

Hello PJ,

I realize that you would like to continue customizing your sheets like you did for others but as my colleague mentioned there must have been an update that will automatically remove customized fields. Until this limitation is addressed, the workaround for this is creating a copy of the sheet and amending it to prevent the integrated sheet from resetting. Is it possible for you to use this method instead of the current one you have in place? Do let us know.


Thank you,
Sean

The proposed alternative would require manual interventions to organize the data, something our team would like to avoid, hence why our team highly values Jotform's ability to intelligently append field data as long as the Spreadsheet's headers/column would match the appropriate field names. Thi is one of the great reasons why we chose Jotform, as we are receiving submissions more than our team could handle at a time, and by marking and plugging more detailed information of each row (that we can further customize via spreadsheet functions) we can keep up with the flow of requests.

As it is, this thread would then be a suggestion/feature request. By not allowing the integrated sheet to be edited and by resetting instead of appending, users such our team would lose quite a lot of work agility. 

I will start by replying with my own questions above since you have confirmed both of them:

1. add new columns to spreadsheet and keep them even after a submission is created

- To achieve this, please add empty and hidden fields with the labels that you would like to have new columns created in your spreadsheet.

This will allow you to 'add' columns to your spreadsheet without breaking the integration.

Filling them out should not be causing any issue even if you do it directly.

2. add new sheets to the spreadsheet and not have them deleted when entry of a new submission is made.

I am not sure if this would break the integration, but I guess that the key point here would be to create integration, modify the spreadsheet by adding more spreadsheets, making a submission.

Even if I said that it can be modified, I would now like to say that what my colleague Jonathan above said is true and that any modification of the original file can and will most likely cause the integration to stop working properly.

Do let us know if the steps above work for you.

Sorry for the late response. Even up to this day, the suggested solution does not work. For the mean time, we are relying to Zapier to push the submissions on a separate sheet while still allowing us to add any necessary columns for better data monitoring.

No worries.

I think our developers made the aforementioned update to address Google Spreadsheet related issues such as when making changes to the sheet that breaks the integration. But I have just moved your last reply to a new thread. Here's the new link: http://www.jotform.com/answers/512271. Will submit it to our developers, for us to know their advice and how they will address this matter.

Thanks

That is much appreciated. Thanks Welvin!

Hello PJ,

On behalf of my colleagues, you are welcome.

Do get back to us if you have any questions.

Thank you!