Google Sheets integration changes the Column order

When I select Google sheets integration I have discovered that Google changes the column order, in particular, the first 3 cols are changed when the IP address is moved to one of the last columns.

This is a MAjor Major issue for me as I have Excel formula that works on the resulting form submissions and they will not work when I try to use them with Google.

And it is important that they work within Google as others need to view them. The formula were developed on Excel and then imported into Google, the changes are significant...

Please fix ASAP

JohnW

Hello @ejwjohn

Thank you for your message. After the Spreadsheet is created on Google Sheets, you can manually move the columns. The submissions will continue working and you can use your formula.

I hope this helps. Let us know if you need further assistance.

Oh, I wish it was so simple, Excel  and Google sheets are significantly more intelligent than that, if you change a single cell location then any active formula will change to mirror the change. 

Hence moving columns as you suggest will not and does not work.

And why is google changing the col location anyway?

JohnW

We would like to apologize for any inconvenience. I'm trying to reproduce the issue you are having with form's Google Spreadsheet integration and I think I may be missing some details.

Can you tell us how exactly is the IP address column in the spreadsheet moved, please? I remember the IP address being one of the first columns of the integrated Google Spreadsheet before. But now, the IP Address is one of the last columns in the spreadsheet. Is that what you are referring to?

Is the issue happening right after you integrate the form with your Google Spreadsheet? Or, it happens after a few submissions? Also, you mentioned about the imported formula from Excel. Was that applied to the spreadsheet integrated with your form? Or is the spreadsheet integrated with your form imported to another sheet where the formula is being applied to?

Please share the link to the form you are working on. Please provide us any details that you think will help us in investigating the issue you are having.

We will wait for your response.

I will try and answer your questions in the order you asked them

When you specify that you wish to have the IP address included in the submission it clearly places it as the second field, it comes directly after the Submission date, When you download the submissions using Excel format, the resulting Spreadsheet has the IP address inserted in the second Column.

However, if you then look at the resulting Google Sheets created via the integration the IP column is 3rd from the Last

The issue started at the very beginning and continues for every new submission.

Regarding the Excel Formula, they were developed using Excel on my local computer, which I used with the downloaded Excel spreadsheet of all the submissions. Now that they are verified as working I have uploaded that sheet into Google Drive, where I can integrate that sheet containing the formula into a new google spreadsheet, then using google sheet formula I can copy data from the Google Sheet created by the integration into my spreadsheet with all the Formula created to analyse the submissions.

This is the form that i am using  https://form.jotform.com/200392056174046

JohnW

Based on my understanding, your issue is that the downloaded Excel and Google Spreadsheet integration places the IP address in uneven column order? If that is correct, note that the Excel generated from the server side when you download them from your form's submissions page has a different back-end configuration than with Google Spreadsheet integration so I believe that's the reason why. Google Spreadsheet always places the IP in the 3rd column from the last, this is a fixed setting and can't be changed while the downloaded Excel places the IP in the 1st column.

I understand that by design-wise, it is good to have them in the same column order but with the downloaded Excel, you have all the control to change the column order using any application that can open Excel files.

Is there any other issue going further when you import it with Google Drive after re-ordering the columns to match with Google Spreadsheet?

Sorry, but I do not consider this response helpful, where you have different back end servers is not my issue and concern, However, what I do expect your platform to do is deliver consistency, and this is plain to see you have failed big time.....

Using Jotform is slowly becoming more of a challenge than a positive asset. when the submissions are completed and they are updated to Google Drive, it was the plan to have a fully automated system whereby, the submissions are analysed and a summary report is automatically generated for other people in the organization to view as they have no need to see all the detail. 

Unless I can find another way to create this automatic environment I will have failed in one of my objectives.. not something that pleases me.

A very very frustrated JotForm user.

JohnW

We understand that this could be really frustrating and we would like to apologize if the Google Spreadsheet integration didn't meet your expectations. As the Google Spreadsheet places the IP address in the 3rd column from the last and its a fixed setting that cannot be changed, perhaps another option that you can try to do is update or adjust the formula in the new Google Spreadsheet you created to analyze the submission data from the spreadsheet integrated with your form. When you need to get the submission data that were analyzed in an Excel file, you can download it straight from Google Spreadsheet, not from the submissions page of your form.

It took me the best part of a week to sort out these formulae, now I have to go back dismantle them and start again....JotForm thanks a bunch, what a fiasco.

I will definitely be looking around for a more professional outfit the next time I need to create a form.

Again, apologies for any inconvenience. 

I just would like to ask if you have tried manually moving the IP Address column of the integrated spreadsheet to the first columns of the spreadsheet before applying your formulae? I have tested and I can confirm that the integration doesn't break if you do this.

Also, have you tried copying the data per column from the integrated spreadsheet to the new spreadsheet where you have the formulae?

The keyword here is Automatic if I have to intervene and do anything manually, then what is the point???

I have resigned myself to recreating the formula this thread is closed, your continual attempts to respond are not helping......and i believe wasting both of our time.