- mcwatsonAsked on November 10, 2014 at 09:44 AM
Merging two Google Sheets
I have two Jotforms collecting related by different pieces of information. Sheet 1 is collecting the details of calls coming in and sheet 2 is collecting the updates or resolutions to those calls. I've gone down this route as never worked out how to retrieve the original call details into a form so the user can update or close the call.
I have the output of these two forms going into 2 separate google sheets (being that you can't feed a form's info into a tab of an existing spreadsheet).
I now want to report across those two sheets but can't find a simple way how. IMPORTRANGE is good but doesn't auto refresh unless I manually update a cell on the sheet. VLOOKUP doesn't work across two sheets, works fine in two excels but not in two sheets.
Any ideas anyone on how to solve this?
Form 1 (Helpdesk Call Logs) is integrated into Google sheets into sheet called Helpdesk Call Logs.
Form 2 (Helpdesk Call Updates) is integrated into Google sheets into sheet called Helpdesk Call Status.
Either I want a better way of collecting the output from the above 2 forms into one location or I want a way of combining the data from the two Google sheets.
- CharlieAnswered on November 10, 2014 at 11:24 AM
Unfortunately, it's not possible right now to have two forms to update one Google spreadsheet. Although, you are right that you can do this by using the importrange function, you can also check about it here: https://support.google.com/docs/answer/3093340?rd=1.
If you are looking to merge/combine submissions, you can try the following suggestions:
1. Combine Submissions App: You can take advantage of this app which will allow you to combine the submissions of multiple forms in one csv file. Please take a look at the following URL : http://apps.jotform.com/app/combine_submissions
2. Report: You can create HTML Table listing or grid listing report. You can open these reports and copy the submission records to manually merge them. The following guides should help you create reports:
These are the options that I found out. There is no automated way to merge the submission records right now.
I hope this gives you an idea.
- mcwatsonAnswered on November 10, 2014 at 11:31 AM
Thanks for the response. Unfortunately, none of these work for me.
The IMPORTRANGE function works but doesn't automatically update when receiving a submission, it only updates if you manually change or manually update a field in the spreadsheet.
The Combine Submissions App only works if you manually run it and even then, put's all the date in a not very user friendly way. But the main problem here is I want a report to be automatically updated upon a new submission coming through.
Likewise, the HTML method is all very manual so not appropriate.
If I could just find out why the IMPORTRANGE doesn't update on it's own, it would solve my problem. Would be good if at some point, Jotform also provided the functionality to feed into a tab into an existing spreadsheet. It's a great service in general.
- CharlieAnswered on November 10, 2014 at 12:07 PM
Thanks for the update. I tried it on my end and I think I see what you were mentioning. The master sheet or the form that uses import seems to not update the cell when an actual filling out of the cell on the origin form has not been made.
I'll try looking for a work around on this, although this is more of a functionality of the Google Spreadsheet rather than JotForm, but I'll let you need if I come up with a working resolution later this day.
- mcwatsonAnswered on November 10, 2014 at 05:05 PM
Your absolutely right, the fact that IMPORTRANGE doesn't work very well is wholly a Google thing, not Jotform. I just hoped that as Jotform cannot feed two forms into one sheet or into two tabs into the one sheet, then others may have used the IMPORTRANGE workaround and found a way of getting it to work effectively.
- JotForm SupportMike_TAnswered on November 10, 2014 at 06:27 PM
You can try workarounds from the following http://webapps.stackexchange.com thread:
Alternatively to our Google Spreadsheets integration, it might be possible to use Zapier integration on forms to update a single Google Spreadsheet from different forms.
- elgabrielloAnswered on February 14, 2015 at 03:24 PM
I am experiencing a similar issue. I am linking a 2nd gdoc sheet to the one that is receiving the jotform results through an importrange formula and it does not seems to update automatically.
Have you found a way to work around this issue ?
Moreover, importrange seems to work on manual update but not on new form filling.
- ruslanAnswered on March 22, 2015 at 10:57 PM
what if we create many forms with their own SS response pages. Now we can attach a simple Google Script to each of them. The code will execute another script that is in our main SS. Now the tricky part is, the script can also tell the main script - from what form to borrow the response (simply transfer the name of the subform into a string that look from where to read the information). I am a noob, so I want to implement this myself I just need to know how to run a script from another script. I think it's possible since script can simply sits on Google Drive by itself.
- CharlieAnswered on March 23, 2015 at 01:01 AM
Thanks for sharing your inputs here. I'm not that familiar with scripts on Google Drive, unless you are referring to running functions and scripts on Google Spreadsheet, that one is possible where you can import multiple spreadsheets to your main spreadsheet with scripts. Although, I'm not that familiar with Spreadsheet scripts but you can find more about it here: Google Spreadsheet Functions and App scripts.
We would also like to advise that you open your own thread here in the forum when you hare questions or concerns so that we can better assist you.
- eyeconicAnswered on July 25, 2016 at 06:23 PM
Actually this can be achieved by using the import range, if you set the destination spreadsheet to update automatically in the settings.
Go to File > Spreadsheet Settings > Re-Calculate > "On Change and Every Minute/Hour"
This will cause it to constantly pull the new data from the other spreadsheets regardless if there is a change detected or not manually.
I hope this helps! It helped me in a similar situation importing a field that was using an auto date range calculation to determine number of days since a specified date.