G-sheet to Excel and sheet-2-form widget for pre-populating form

Hello,

For my two forms, 90416458290964, 83354135430955, I use sheet to form widget. Now I have an integrated G-sheet for each.

About 65% or more of the field data is repetitive for each club, so the sheet to form widget is perfect for this task.

I want to take the integrated data from the G-Sheets into an Excel sheet and use this Excel sheet for the sheet to form widget. This is actually no big task, but it has a drawback, the updated Excel sheet has to be uploaded manually to the two sheet to form widgets to maintain the data for the widgets. 

I was thinking of using a condition to notify me when a new submission was made to get me to do this in a timely manner. This also presents a issue for me, how do I know if any changes were made to the fields that should remain constant for the most part, so, that I'm not uploading a new sheet with no changes, gets to be a nuisance and old quickly. 

An example of this occurred recently when a club submitted 13 applications. The first one had new data for the fields that were empty, then he had to fill these with the same info for some fields 12 more times. An automated system would do this for the next 12 and not have to send me 13 messages that a new submission was made. If I was on the ball I could quickly upload the new excel sheet and he then may only have to do 6 of them.

So, my question is, can you think of an alternate method for accomplishing this, like an automated updating of the Excel sheet for the widget ?

Also, It would also be helpful if a G-sheet could be used for the widget.

Thank you,
Robert

The widget does not have a way of pulling data from an external source in any manner, including for the purpose of updating the uploaded spreadsheet. 

Rather than attempting workarounds using spreadsheets, something like an option to redirect to a prefilled form would likely be better:

https://www.jotform.com/help/351-How-to-Automatically-Pass-Form-Data-to-Another-Form

Add a field to the form to allow the user to select whether or not they would like to be redirected back to the form with their information filled, then conditionally redirect them back to the form with the data passed from the previous submission.  Then they can modify whatever they need for the new submission and resubmit the form.

https://www.jotform.com/help/317-Change-Thank-You-Page-URL-Based-on-a-Condition

Hi... 

So my ul redirect would be very long to fill in all the fields again...this will take some time to create... OK then... 

So, the selection is made with an option on the Thank you page, create a link that has all the info to copy and paste to the next submission and it redirects to the filled form... am I close?

So, I would just make a copy of each of my forms, so fields are all the same, but with different element id's. 

With this method any number of forms could be submitted with the redirect and pre-populated

I still want to integrate, witch form is integrated then, original or copy?

Why don't we ask the developers if it would be possible to have the widget pull data from an external source. A feature request... If possible.

Thank you,
Robert

I will submit a feature request but traditionally we have limited the ability to pull data into the form from external sources for security reasons.  This one might be a bit of a long shot but I'll definitely put it through.

As for my suggestion, the same form would be used.  Here is a quick example form to show how it would work:

https://form.jotform.com/90495219028965

And here is the conditional thank you page URL:

https://form.jotform.com/90495219028965?name[first]={name:first}&name[last]={name:last}&email={email}&myText={myText}

It takes the data submitted and places it back into the form when it is reloaded.

OK... I see what is occurring, I think, hard for this 72 year old head to get around until I actually give it a shot. The alternate thank you page condition helps me understand. I just counted and have over 60 fields that I would repopulate. I'm just thinking of all the script to do that... sounds a bit daunting... ech!, hay! you could do it!

My form is two pages, could it auto move to the second page or would the user have to click on next page even if it is set to auto?

I was reading some of the questions asked and need some clarification, more often then not my clubs have to go back and enter some data at a later time, is it safe to assume that the edit submission function will still work? 

Thank you,
Robert

The user will need to click on the Next button in order to go to the next page, example: https://form.jotform.com/90498589295984 

Adding the "jumpToPage" parameter would be an option: https://form.jotform.com/90498589295984?jumpToPage=2&name[first]={name:first}&name[last]={name:last}&email={email}

But the user won't be able to see page 1 due to a bug that has been already reported to our back-end:

So, having the user clicking on the next button would be the best option for the moment.

" is it safe to assume that the edit submission function will still work? "

Yes, the edit submission option is something available to all submissions, so it will still work for those submissions that were populated via URL parameters.

Hi,

So I'm using notepad++ to construct my url. , but I'm stuck early on. I created a clone 90503848921965 and here is what I have so far;

https://www.jotform.com/build/90503848921965?Host Club={club}&AAC No.(1)={aacNo1}&Club Address={club_add}&Club City={C_city}

Just to be sure I have this correctly formatted, is this little bit OK?

Thank you,
Robert

It is not ok, because you used the URL from the Form Builder and you should use a form URL.

Filed names are also not correct.

Here is the correct format:

https://form.jotform.com/90503848921965?Club={club}&aacNo1={aacNo1}&club_add={club_add}&C_city={C_city}

The following guides can help you:

How to Find Field IDs and Names

How to Automatically Pass Form Data to Another Form

Prepopulating Fields to Your JotForm via URL Parameters

Hi, 

That was helpful. Now I'm having a problem with this bit, some radio buttons:

TrialSec?={TrialSec?:0},{TrialSec?:1}&Read={Read:0},{Read:1}&Agrees={Agrees:0},{Agrees:1}

Am I'm doings this correctly?

Thank you
Robert

Hey Robert - Are you trying to prepopulate the form with your own data or data from other forms? If with your own data then we have an easy way to do that and you do not need to generate URLs manually.

Just go to https://prepopulate.jotform.io/ and then select the form you want to prepopulate. Once finished selecting the radio buttons or filling out the fields then generate the URL.

Guide: Prepopulating Fields to Your JotForm via URL Parameters

You'll also see the field IDs of your form once the URL is created.

Hi,

from other forms

This could be helpful, but not for this form.... It did help me with the field names though. 

Do want to clarify something. For radio buttons, example has two options Yes and No. I did this for them:

Example Radio button name is radio1

Will you by a new radio?  [Yes]  [No]

1)  radio1={readio1:Yes},{radio1:No}&  Or should I use

2)  radio1={readio1:0},{radio1:1}& 

I have been thinking of using option 1)

Thank you
Robert

Since radio buttons can be able to select only one choice, we need not mention any choice in the parameters and just field name is sufficient as shown below.

radio1={radio1}

The method that you were referring to can only be used for checkboxes which allow multiple selections.

Hope this information helps! 

Hi,

Yes it is very helpful.

What I've been testing is if the applicant would like to submit another form I send them to a new one that is simpler to manage and has no page break.

form 1: 90503848921965.

If they select additional submissions it redirect to form 2 90514557343962. The data from form 1 is copied to form 2 fields.

Some of it works and most of it fails. The section that is failing I can't find anything wrong with. 

Here is a small piece of the failing code, none of these fields get filled and I was sure of their names;

 https://form.jotform.com/90514557343962?Club={Club}&aacNo1={aacNo1}&club_add={club_add}&C_city={C_city}&Prov1={Prov1}&PC1={PC1}&email1={email1}&website={website}&tele1[full]={tele1[full]}&applicant={applicant}&aacNo2={aacNo2}&add2={add2}&city2={city2}&prov2={prov2}&pc2={pc2}&email2={email2}&tele2[full]={tele2[full]}&cell2[full]={cell2[full]}&TrialSec?={TrialSec?}&Read={Read}&Agrees={Agrees} ~ to green section ~

This small piece after above is working:

&secretarysName={secretarysName}&&aacNo3={aacNo3}&add3={add3}&city3={city3}&prov3={prov3}&pc3={pc3}&tele3[full]={tele3[full]}&cell3[full]={cell3[full]}&email3={email3}&Site={Site}&details={details}&Add4={Add4}&details={details}&city4={city4}&prov4={prov4}&pc4={pc4}&tele4[full]={tele4[full]}&

Then a large piece below this is failing. Now I realize why the radio-boxes don't work and why the check-boxes didn't also so I will work on that now. But section in yellow has me baffled.

Thank you, Robert

I think it is due to the ? in TrialSec?={TrialSec?}.  Try changing that unique name to just TrialSec and instead of TrialSec?, then update your URL to reflect the change and it should work:

https://form.jotform.com/90514557343962?Club={Club}&aacNo1={aacNo1}&club_add={club_add}&C_city={C_city}&Prov1={Prov1}&PC1={PC1}&email1={email1}&website={website}&tele1[full]={tele1[full]}&applicant={applicant}&aacNo2={aacNo2}&add2={add2}&city2={city2}&prov2={prov2}&pc2={pc2}&email2={email2}&tele2[full]={tele2[full]}&cell2[full]={cell2[full]}&TrialSec={TrialSec}&Read={Read}&Agrees={Agrees}

Thanks, big help, changed a number of them with special char. Working 99% now. having a problem with this check-box, I get nothing.

LastInspect={LastInspect:0},{LastInspect:1},{LastInspect:2},{LastInspect:3},{LastInspect:4}

Thank you, Robert

It should be like this only: &LastInspect={LastInspect}. You do not have to add 0,1,2,3 and multiple values field IDs. One container will get all the value in the radio button.

Give it a try and let us know how it goes.

Hi,

Sorry but this is not a radio button it is a checkbox as I indicated. I am now aware of how radio box is used as per Kiran  above.

Also an issue with this bit: &Surface={Surface}&

Regards, Robert

The "&Surface={Surface}&" parameter is okay, so it should pass the data from form 1(90503848921965) to form 2(90514557343962), is it not working? Let us know if you still need help, we will be glad to assist you.

Here is the complete code only highlighting piece in yellow not working:

LastInspect is now working.

https://form.jotform.com/90514557343962?&Club={Club}&aacNo1={aacNo1}&clubadd={clubadd}&city1={city1}&prov1={prov1}&pc1={pc1}&email1={email1}&website={website}&tele1={tele1}&applicant={applicant}&aacNo2={aacNo2}&add2={add2}&city2={city2}&prov2={prov2}&pc2={pc2}&email2={email2}&tele2={tele2}&cell2={cell2}&TrialSec={TrialSec}&read={read}agrees={agrees}&secretarysName={secretarysName}&aacNo3={aacNo3}&add3={add3}&city3={city3}&prov3={prov3}&pc3={pc3}&tele3={tele3}&cell3={cell3}&email3={email3}&Site={Site}&details={details}&add4={add4}&city4={city4}&prov4={prov4}&pc4={pc4}&tele4={tele4}&Lim/Unlim={Lim/Unlim}&in/out={in/out}&WdayWend={WdayWend}&TCD={TCD}&TSD={TSD}&TED={TED}&rings={rings}&Surface={Surface}&Sizes={Sizes}&1stTria={1stTrial}&NewSite={NewSite}ChkNewSite={ChkNewSite}&site5yr={site5yr}&ChkNewEqu={ChkNewEqu}&Equip5yr={Equip5yr}&JmpsChk={JmpsChk}&JmpChkDoneOn={JmpChkDoneOn}&ChkSchd={ChkSchd}&WhyNot={WhyNot}&LastInspect={LastInspect:0},{LastInspect:1},{LastInspect:2},{LastInspect:3},{LastInspect:4}&DateEquChk={DateEquChk}&EquChkBy={EquChkBy}&DateSiteChk={DateSiteChk}&SiteChkBy={SiteChkBy}&NumJudges={NumJudges}

Thank you, Robert

I just cloned your form 1, and set your complete URL with parameters:

https://form.jotform.com/90514557343962?&Club={Club}&aacNo1={aacNo1}&clubadd={clubadd}&city1={city1}&prov1={prov1}&pc1={pc1}&email1={email1}&website={website}&tele1={tele1}&applicant={applicant}&aacNo2={aacNo2}&add2={add2}&city2={city2}&prov2={prov2}&pc2={pc2}&email2={email2}&tele2={tele2}&cell2={cell2}&TrialSec={TrialSec}&read={read}agrees={agrees}&secretarysName={secretarysName}&aacNo3={aacNo3}&add3={add3}&city3={city3}&prov3={prov3}&pc3={pc3}&tele3={tele3}&cell3={cell3}&email3={email3}&Site={Site}&details={details}&add4={add4}&city4={city4}&prov4={prov4}&pc4={pc4}&tele4={tele4}&Lim/Unlim={Lim/Unlim}&in/out={in/out}&WdayWend={WdayWend}&TCD={TCD}&TSD={TSD}&TED={TED}&rings={rings}&Surface={Surface}&Sizes={Sizes}&1stTria={1stTrial}&NewSite={NewSite}ChkNewSite={ChkNewSite}&site5yr={site5yr}&ChkNewEqu={ChkNewEqu}&Equip5yr={Equip5yr}&JmpsChk={JmpsChk}&JmpChkDoneOn={JmpChkDoneOn}&ChkSchd={ChkSchd}&WhyNot={WhyNot}&LastInspect={LastInspect:0},{LastInspect:1},{LastInspect:2},{LastInspect:3},{LastInspect:4}&DateEquChk={DateEquChk}&EquChkBy={EquChkBy}&DateSiteChk={DateSiteChk}&SiteChkBy={SiteChkBy}&NumJudges={NumJudges}

And, it works fine, here is my clone version: https://form.jotform.com/90518335395967 

Here is the redirection that generated the values:

https://form.jotform.com/90514557343962?&Club=test&aacNo1=000&clubadd=test&city1=test&prov1=QC&pc1=a3a%203r3&email1=test@test.com&website=test@test.com&tele1=333-333-3333&applicant=test&aacNo2=55555555&add2=test&city2=test&prov2=PE&pc2=a3f%202f3&email2=test@test.com&tele2=333-333-3333&cell2=333-333-3333&TrialSec=Yes&read=Noagrees=Yes&secretarysName=test&aacNo3=3333&add3=test&city3=test&prov3=QC&pc3=a3r%203a3&tele3=333-333-3333&cell3=333-333-3333&email3=test@test.com&Site=test&details=test&add4=test&city4=test&prov4=BC&pc4=t3t%203t3&tele4=333-333-3333&Lim/Unlim=Limited&in/out=Outdoors&WdayWend=Week%20day%20(Mon%20~Thur)&TCD=14%20Feb,%2019&TSD=22%20Feb,%2019&TED=21%20Feb,%2019&rings=1&Surface=This%20is%20my%20test.&Sizes=100&1stTria=No&NewSite=NoChkNewSite=No&site5yr=No&ChkNewEqu=&Equip5yr=No&JmpsChk=No&JmpChkDoneOn=14%20February,%2019&ChkSchd=&WhyNot=&LastInspect=Equip.%205%20Yr.,,,,&DateEquChk=21%20February,%2019&EquChkBy=test&DateSiteChk=21%20February,%2019&SiteChkBy=test&NumJudges=1 

Can we try submitting your form 1, to see how it works?

Well that's odd.

Now I do have a default for this field "Grass". I just ran a test again and all I get is Grass when I check the redirection values and it shows  &Surface=Grass%20&%20Sand which is what I entered, "Grass & Sand" So is the default "Grass" interfering perhaps? I can remove it and try again. As you say it should work.

Robert

https://form.jotform.com/90514557343962?&Club=Cariboo%20Agility%20Team&aacNo1=C224&clubadd=4906%20Peasin%20Court%20PO%20Box%2010211&city1=108%20Mile%20Ranch&prov1=BC&pc1=V0K%202Z0&email1=caribooagilityteam@shaw.ca&website=www.terroxz.wix.com/caribooagilityteam.com&tele1=250-791-7330&applicant=Roxanne%20Ziefflie&aacNo2=548&add2=4906%20Peasin%20Court%20PO%20Box%2010211&city2=108%20Mile%20Ranch&prov2=BC&pc2=V0K%202Z0&email2=terroxz@shaw.ca&tele2=250-791-7330&cell2=250-706-3126&TrialSec=Yes&read=Noagrees=Yes&secretarysName=Roxanne%20Ziefflie&aacNo3=548&add3=Box%2010211&city3=108%20Mile%20Ranch&prov3=BC&pc3=V0K%202Z0&tele3=250-791-7330&cell3=250-706-3126&email3=terroxz@shaw.ca&Site=100%20Mile%20Baseball%20Diamonds%20(Lumberman%E2%80%99s%20Park)&details=TESTING&add4=End%20of%20Wrangler%20Way,%20West%20Off%20Hwy%2097&city4=100%20Mile%20House&prov4=BC&pc4=V0K%202E0&tele4=&Lim/Unlim=Unlimited&in/out=Outdoors&WdayWend=Week%20day%20(Mon%20~Thur)&TCD=5%20May,%2018&TSD=7%20Dec,%2018&TED=15%20Dec,%2018&rings=5&Surface=Grass%20&%20Sand&Sizes=100%20x%20111,%20100%20x%20105&1stTria=No&NewSite=NoChkNewSite=No&site5yr=No&ChkNewEqu=&Equip5yr=No&JmpsChk=Yes%20-%20Scheduled&JmpChkDoneOn=21%20February,%2019&ChkSchd=21%20February,%2019&WhyNot=&LastInspect=New%20Site,Site%205%20Yr.,New%20Equip.,,&DateEquChk=21%20February,%2019&EquChkBy=Adam&DateSiteChk=21%20February,%2019&SiteChkBy=George&NumJudges=5

I use form 2 to make and other submission and changed it to "Gravel" and it worked perfectly.

Is the issue persists or is it okay now? If the issue still persists, could you let us know if we can send a test submission to your form 1 to test it from our end?

Thank you.

Hi,

I haven't tested the issue with Surface element today.

Hi,

I am ready to go live with this now.

Thanks for all the help everyone,
best
Robert