How to Use the Spreadsheet to Form Widget

  • omaruko
    Asked on October 28, 2017 at 2:21 AM

    Hi there


    I refer to the following guide

    https://www.jotform.com/help/442-How-to-Use-the-Spreadsheet-to-Form-Widget


    1.What is the reason the sides are not aligned?

    The following URLs are from my trial.

    https://gyazo.com/d3e4d0cece7ea8471c2d7d474d7b17fe

    https://gyazo.com/fb45847804f8ee89b1a89ac641003165



    2.Comparison column of spreadsheet with field labels

    The sides are not aligned

    https://gyazo.com/6d82c9338a853d134f749dba394e1037

    A.Before I upload the data, must I enter the data into the blank spreadsheets 

    For example, the following URL

    https://gyazo.com/b41f606a11192955a637ffbc19f7e12e

    I have omitted column C and thereafter.

    3.Populate fields from spreadsheet

    https://gyazo.com/51bc05b35e6d04d26dfbfd1ecb256e38

    How are Populate fields from a spreadsheet?

    4.Access code column

    https://gyazo.com/ba3e351e8249675fce49f25a8f7fe5b2

    How this dialog appears on the display?

    Looking forward to having your reply.

  • TREVON
    Replied on October 28, 2017 at 6:05 AM

    Than you for contacting us. Due to the number of questions asked I will answer them inline green colour.

    1.What is the reason the sides are not aligned?


    The following URLs are from my trial.


    https://gyazo.com/d3e4d0cece7ea8471c2d7d474d7b17fe


    https://gyazo.com/fb45847804f8ee89b1a89ac641003165

    Kindly share the form ID so that I can have a look at the form and fix the alignment for you. But from what I can see based on your images some questions are wider than other questions. Below is a link to a sample I have created.

    Kindly feel free to clone it and make modifications.https://form.jotform.com/73002005581947

    https://form.jotform.com/73002005581947

    2.Comparison column of spreadsheet with field labels


    The sides are not aligned


    https://gyazo.com/6d82c9338a853d134f749dba394e1037


    A.Before I upload the data, must I enter the data into the blank spreadsheets 


    For example, the following URL


    https://gyazo.com/b41f606a11192955a637ffbc19f7e12e


    I have omitted column C and thereafter.

    Before you upload the data you must enter the date in the spreadsheet as these will be the data that will be populated to your form.



    3.Populate fields from spreadsheet


    https://gyazo.com/51bc05b35e6d04d26dfbfd1ecb256e38


    How are Populate fields from a spreadsheet?

    Kindly elaborate on this question.


    4.Access code column


    https://gyazo.com/ba3e351e8249675fce49f25a8f7fe5b2


    How this dialog appears on the display?


    To see how the dialogue appears kindly check on the illustration below.

    1509184572dialogue appear Screenshot 10

  • omaruko
    Replied on October 29, 2017 at 12:02 AM

     @TREVON


    Thank you for your kind reply.


    1.>Kindly share the form ID so that I can have a look at the form and fix the alignment for you

    https://www.jotform.com/build/72993983981479

    Thank you for your kindness.The above is my in-process form.


    2.>>The sides are not aligned

    Please refer to the following URL.

    https://www.jotform.com/build/72993983981479

    This is the same one as 1.

    I forgot to ask you about Unique code.

    That is

    The reason why fields do not have the Unique code is the form owner send the respective user the blank form.

    The respective owner receives and fills the form up, then it back the form owner.

    Under the circumstance, no need to have Unique code in the field.

    Unique code means company name and so on.

    Is my above understanding correct?


    3.>Populate fields from spreadsheet

    Kindly elaborate on this question.

    That is what is the distinction between my in-process form and the part of your guide.

    https://gyazo.com/75af8eb1db07ac2d9508b922c988212a   My in-process form

    https://gyazo.com/e3178d48048fbe61f4f943f3fa9ddc38   The part of your guide


    4.>>Access code column

    OK, I have confirmed it.


    Hope you understand my question.


    Looking forward to having your further peply.

  • Mike_G JotForm Support
    Replied on October 29, 2017 at 3:17 AM

    The Spreadsheet to Form widget is used if you want to prepopulate the form with data from an outside source (excel file) that is uploaded in the widget's settings.

    1509259434t02 41 15 Screenshot 10

    This means that the form fields will be prepopulated with data from an Excel file provided that the correct "Unique Code" (Access code) is entered in the form. The "Unique Code" serves as the reference id of the data that will prepopulate the form. Here's a clone version of the form in this guide — How-to-Use-the-Spreadsheet-to-Form-Widget

    https://form.jotform.com/73011089790961

    If you enter "unique1" in the widget's textbox field and click the "Prepopulate Fields from Spreadsheet" button, you will see that the fields will be pre-populated with data from the spreadsheet uploaded in the Spreadsheet to Form widget.

    1509260464t02 58 Screenshot 21

    The widget will not work without declaring an Access Code Column (unique code) because that is used as the widget's reference column.

    1509260814t03 03 03 Screenshot 32

    The set up shown in the screenshot above means that the Column A is where the Access codes (unique codes) are placed in the Excel file.

    As for the "Populate fields from spreadsheet", it will be different based on what you input as the widget's "Fill button text"

    1509261235t03 09 17 Screenshot 43


    I hope this helps. However if, in any case, I have misunderstood your concern, please feel free to let us know. Also, please let us know what exactly are you trying to accomplish so we can check if there are other ways to achieve it.

  • omaruko
    Replied on October 31, 2017 at 1:11 AM

    @Mike_G   @TREVON

    @Mike_G 

    Thank you for your reply

    Based on your last reply. let me confirm and ask you again.

    1.Form owner shall prepare to fill spreadsheet or excel.

    https://gyazo.com/9312781b886248939e04a2b5a3b10b02

    2.Form owner shall fill only unique code.

    Other than unique code, sheet columns should be matched the same name with fields.

    I refer to the following URL,

    https://www.jotform.com/help/442-How-to-Use-the-Spreadsheet-to-Form-Widget

    Of which, I point the following out 

    Full name > Email > Time ~Then, your form field labels should be exactly the same:

    3.Field labels

    As I follow your guidebook, the owner shall fill only the dynamic name such as full name and email and so on.

    The respective user shall fill the form and send the form owner back after user receives the blank form from the owner.

    @TREVON

    1.>>Populate fields from spreadsheet

    This one shall become out in the dialog box, the spreadsheet to form widget if the owner has the following

    dialog.

    https://gyazo.com/78c6484abe3c0b30e3d4897aa1414966

    My understanding is that Populate fields from the spreadsheet of dialog shall become out if the following dialog comes out, right?

    Looking forward to having your further reply.

  • Nik_C
    Replied on October 31, 2017 at 3:58 AM

    1. That is correct, form owner should prepare the Sheet and upload it to the form.

    2. Labels in the form must match column names in Sheet. Unique ID field doesn't necessarily need to match the name in the Sheet, you just have to specify where is it in the Sheet:

    1509436589Screen Shot 2017 10 31 at 8 Screenshot 10

    3. Not actually true, form owner have to create the whole sheet, meaning, everything you want to populate from the sheet to the form you have to input in the Sheet, otherwise, the data will not load. So if you want to prepopulate name, email, phone number from the sheet, for example, you have to fill those in the Sheet. And have the appropriate unique ID for each row.

    Hope it helps.

    Thank you!

  • omaruko
    Replied on November 1, 2017 at 9:40 AM

    @Nik_C

    Thank you for your kind reply.

    1.>That is correct~ 

    I have confirmed it.

    2.>Labels in the form~

    My understanding is as follows.

    Form owner must create sheet where he/she knows beforehand, such as unique code and name,

    while the place he/she does not know keep their blank.

    Please refer to the following URL

    https://gyazo.com/e542bf99a1705a83d08ed57083f8e3b9

    Of which, A means Unique code, B means location, regarding for C owner create Yes or No, while, user has a choice Yes or No.

    If owner completes the work at the spreadsheet side, owner click the button "Update Widget"

    Moreover, let me confirm the following dialog

    https://gyazo.com/51bc05b35e6d04d26dfbfd1ecb256e38

    Populate fields from spreadsheet

    comes out on the display of the dialog after owner completes preparing data from sheet to field

    Owner sends the form to a respective user after owner completing a form at the spreadsheet.

    3. Not actually true, ~

    I roughly got you mean, though, still have the following question as follows.

    >So if you want to prepopulate name, email, phone number from the sheet, for example, you have to fill those in the Sheet.

    What about we leave to the judgment of the user?

    Looking forward to having your reply.

  • Kevin Support Team Lead
    Replied on November 1, 2017 at 11:30 AM

    2. Yes, you need to set up an unique code, but it does not necessary need to be placed in the column A, it can be placed on any other column, but make sure to set such column in the "Access Code Column" section of your field. 

    Once you have done this you will need to update the spreadsheet on the widget as shown on your second link's image: https://gyazo.com/51bc05b35e6d04d26dfbfd1ecb256e38 

    Please note that this needs to be done each time you update the sheet. 

    3. The fields will be pre-populated from the spreadsheet, but the data can still be loaded so they do not need to send necessary the populated info. 

    You may also leave a column empty on your sheet in case you want to leave that question free to your users. 

    If you have more questions, please let us know. 

  • omaruko
    Replied on November 1, 2017 at 11:43 PM

    @Kevin_G

    Thank you for your kind reply.

    2.I have confirmed it.

    3.Is my understanding correct?

    Spreadsheet

    Please assume that Unique code shall be A, B such as the alphabet.

    An owner can place dynamic name beforehand, meanwhile, I am not sure how I place other than a dynamic name such as choice1,choice2 that means owner choose as she/or he likes.

    https://gyazo.com/622e01e1de421326bd3fc5a07946f36c

    Field

    I am wondering whether owner sends blank format all at once or each user.

    Looking forward to having your further reply.


  • Nik_C
    Replied on November 2, 2017 at 3:12 AM

    3. Nothing stops the end user to go and input something else to the field if he/she wants it:

    1509606078screencast Screenshot 10

    I'm not sure to what you're aiming to there, you either prepopulate fields or don't, you can choose which fields will be prepopulated and which will not by filling the Sheet.

    The sheet is solely your responsibility, by the owner of the form, submitters have nothing to do with it. You decide which fields will be prepopulated and which codes will be used to prepopulate those fields (the codes you will give to your users).

    Regarding the choices question, not sure what would you like to do there, but multiple choice fields will be prepopulated from the sheet in the same way like any other field.

    I would advise you to check this guide: https://www.jotform.com/help/442-How-to-Use-the-Spreadsheet-to-Form-Widget, especially the demo form: https://www.jotform.com/71642066575965 since you can test all variations, with regular fields, dropdowns, multiple choice fields etc.

    I am wondering whether owner sends blank format all at once or each user.

    If you were referring to form, you send a form to the end user and you provide them the code from the Sheet that you previously created.

    Hope it helps.

  • omaruko
    Replied on November 5, 2017 at 2:56 AM

    @Nik_C

    Thank you for your reply

    Please refer to the following URL.

    https://gyazo.com/f7c6685403a4e67a9cc64e7f69b85cef

    Based on your image, let me ask you further confirmation

    That is

    Once the owner find unique code, she/he clicks Populate field button,

    full name and email are automatically loaded.

    Each end user fills the blank such as short text entry of the format after she/he is from the owner.

    I am not sure how we transact the time.

    Looking forward to having your reply.


  • Nik_C
    Replied on November 5, 2017 at 4:35 AM

    Each end user fills the blank such as short text entry of the format after she/he is from the owner.

    I'm afraid I didn't understand that sentence. If you were referring to other fields, that are not prefilled from the sheet, those fields can be freely filled by the submitter. Only fields defined in the Sheet will be prepopulated in the form.

    Regarding the time field, you can download a demo sheet from the guide: https://www.jotform.com/help/442-How-to-Use-the-Spreadsheet-to-Form-Widget

    And see how the time field is defined:

    1509874165Screen Shot 2017 11 05 at 10 Screenshot 10

    And you can check the field Time in the demo form from the same guide: https://www.jotform.com/71642066575965

    So you can see how it works.

    Let us know if you have any further questions.



  • omaruko
    Replied on November 5, 2017 at 9:00 PM

    To whom it may concern

    Thank you very much for your kind cooperation.

    I will forget about the time. I think this one seems to be not necessarily.

    I shall close this thread.