- steveclampittAsked on July 03, 2015 at 07:57 AM
hi i have been using the import range function to clone a Google sheet, it has been working fine all week and automatically updating the clone sheet, came in today and now it is not working, is there a way round this because the only reason i am using jot form is for the signature widget, the import range function still works and updates fine with a Google form.
- CharlieAnswered on July 03, 2015 at 09:59 AM
If I understand you correctly, it seems like the integrated Google spreadsheet is not cloning to one of your spreadsheets. Is that correct? If the integrated spreadsheet is still fetching the submissions to your form, the problem might be more related to the setup you have using the ImportRange. I would advise checking the function you are using, the spreadsheet key or the name, there might have been changes.
We'll wait for your response.
- steveclampittAnswered on July 03, 2015 at 10:45 AM
the new spreadsheet is not pulling the data across until i go onto the integrated spreadsheet (the one created by jotform) and edit a cell then it updates across to my other spreadsheet with the new submission but the past week it has been automated and i have not needed to change the integrated spreadsheet what has changed
and my formula is fine:
=IMPORTRANGE("1d47MLoDnLX_f51yUbdnhCxncYOx7VAo5q01CJDOZd6A", "responses 1!A:A")
as i said its been working up to today, and i have an equivalent set up with Google forms which is picking up and updating instantly...
o me it looks like Google is not noticing a change has been made until something is changed in the integrated spreadsheet
- JotForm SupportWelvinAnswered on July 03, 2015 at 12:11 PM
I'm sorry, but I'm not a pro with Google's ImportRange function. I've found this solution, please try doing the following.
1. In the spreadsheet, click the "File" option
2. Find and click the "Spreadsheet Settings"
3. Change Recalculation from Onchange to Onchange and Every Minute:
I hope this helps. If not, please let us know so we can look into that further. If possible, share with us the spreadsheets so we can check and try it.
- steveclampittAnswered on July 06, 2015 at 05:29 AM
im afraid not i already have that set up, google is not picking up the jotform entry and for some reason not updating it does not pick up the data unless an edit is made to the cell, is there a setting on jotform i have missed or could use ?
- JotForm SupportWelvinAnswered on July 06, 2015 at 07:16 AM
I've found this solution:
Or this one:
The problem is the same as yours when external file is the source file of the importrange function. I think this should help.
- steveclampittAnswered on July 06, 2015 at 09:23 AM
nothing suggested is working this is weird because as i said it was working for a week then just stopped, what i want to know know is there an easy way to clone or edit the submission spreadsheet without it messing up because i need to run functions and scripts hence why im using the clone otherwise i would just do it on the main document.
- JotForm SupportWelvinAnswered on July 06, 2015 at 10:44 AM
We do not recommend doing any modification in the form spreadsheet. Otherwise, that will break the integration and you have to start over to fix it and that will create a new sheet and there's no way you can re-use the same spreadsheet later.
I'm not sure what's causing that to happened, but based on some results I've found, that is an issue with Google Spreadsheet's ImportRange function. Especially if the data is added through another automation such as the integration. Some used the ImportRange without any issues because the data is coming from the other sheet with manual input so onEdit is triggered.
I think you should add a script in the script editor that would trigger the edit when a data is added in a row in the form spreadsheet. I can't seem to find it, but I think that is possible. I will try to find more link about this problem and let you know.
- CharlieAnswered on September 03, 2015 at 12:56 PM
Hi, for us to better assist you, I went ahead and opened a separate thread for your concern. Please refer to this link instead: http://www.jotform.com/answers/654408. We will address it accordingly.
- ChadAnswered on October 14, 2016 at 08:46 AM
The google sheet add-on, Import Sheet, can also solve this problem and auto update your target sheet with the source data. Check in out here: https://importsheet.com/install
- JonatanAnswered on June 21, 2017 at 05:57 PM
Hi steveclampitt and JotForm support staff,
I'm Jonatan, co-founder of Sheetgo (formerly Import Sheet), to help resolve this issue and adding to what Chad said above, I would like to inform that Import Sheet now is Sheetgo, and have a lot of new functions to transfer data between Google Sheets. Check in out the new functions: https://www.sheetgo.com