Google sheet Integration: Split form data from single field to multiple rows

  • davidzuiddam
    Asked on April 1, 2023 at 4:17 AM

    Hi guys,

    I'm a newbie.

    I'm trying to make a form that lists what consumables are allocated where (to track the consumables). The configurable list widget seems to look like it is doing the right thing but the integrated google sheet doesn't come out right.

    First up the integrated google spreadsheet name links nicely but the worksheet name isn't linking. It is creating a new worksheet. This is possibly because the fields don't match up. Which brings me to the next, more important issue. I'd like the information from the form to populate cells in 5 columns (fig 1). The configurable list widget puts all the all the information from the form into a single cell (fig 2).

    How can I achieve my goal of a form that allows for up to 15 rows of information to be dropped into corresponding columns in my google sheets doc?

    Thanks in advance, David

    Jotform Thread 5036886 Screenshot
  • Rahul JotForm Support
    Replied on April 1, 2023 at 9:30 AM

    Hi David,

    Thanks for reaching out to Jotform Support. You can use Google Sheet Integration for this purpose. Let me show you how to set that up:

    • Open Form Builder and click on Settings
    • Select Integrations option from left and then select Google Sheets1680355634 6428313207d7d  Screenshot 10
    • Scroll down to the Authenticate section and connect with your google account
    • After authentication, you will be getting 2 options, first to create a new spreadsheet or to select existing spreadsheet that exists in your account. You can choose as per your requirement1680355687 6428316723ab0  Screenshot 21
    • Select the form fields to send to Google Sheets and click on Save1680355744 642831a0b5bea  Screenshot 32

    We also have a guide about Google Sheets Integration that you can check out.

    Let us know if you need any more help.

  • davidzuiddam
    Replied on April 3, 2023 at 12:06 AM

    Hi Rahul,

    I'm sorry, but you have completely misunderstood my quire, I already know how to Google Sheet Integration and have been doing so. Maybe I was not clear with my descriptions. I'll elaborate more.

    First up the integrated google spreadsheet name links nicely but the worksheet name isn't linking. It is creating a new worksheet. This is possibly because the fields don't match up.

    I have a Google Sheets doc. named "Copy of Inventory Tracking on ipads - Harrisdale (term 1 2023)". This Google Sheets doc. has 6 tabs, the first tab named "Recording_sheet". I also have a Jotform named "Consumables / Inventory Tracking 2". This Jotform uses a configurable list widget to capture the information that is required. The fields inside the configurable list widget correspond to the columns in the "Recording_sheet" tab of the Google Sheets doc named "Copy of Inventory Tracking on ipads - Harrisdale (term 1 2023)". After linking the Jotform with the Google Sheets doc. using the Google Sheets Integration, the Jotform creates a new tab named "Recording_sheet (1) with all the information in a single cell (see fig. 2 below).


    The second - more important part

    I'd like the information from the form to populate cells in 5 columns (fig 1).

    fig. 11680493548 642a4bec631a6  Screenshot 10

    The configurable list widget puts all the all the information from the form into a single cell (fig 2).

    fig. 2

    1680493684 642a4c741f63b  Screenshot 21

    My quire is:

    How can I create a Jotform to capture the information: 'Date', 'What was allocated', 'Where allocated to', 'quantity' and 'notes / comments'. Then drop the information into the corresponding columns in my Google Sheets doc.?

    Can you please help. Cheers David

  • Rahul JotForm Support
    Replied on April 3, 2023 at 1:23 AM

    Hi David,

    Thanks for getting back to us with the additional information. For your first query about the new sheet is being created for the submissions, please note that the columns in the google sheet must match with form fields. Otherwise, it won't collect the data correctly. As soon as you connect the form with google sheet, it creates a new tab with all the columns matching with form. We recommend using the created sheet automatically by the integration to avoid any issues with the integration.

    As for your other query about getting the data in the same column in the google sheet, this is the intended behavior. One field on the form will send data to a single column in the google sheet. If you wanted to have each data in separate columns, you'll need to add separate fields in the form.

    Let us know if you have any other questions.

  • davidzuiddam
    Replied on April 3, 2023 at 1:43 AM

    Hi Rahul,

    Thank you for your informative response.

    If I have 5 separate fields on my form how can I replicate the 5 fields onto another row?

    My requirement is to capture up to 20 rows of information (see fig. 1, 23/2/2023).

    Is it possible to easily generate new rows in a form, similar to the configurable list widget?

    Cheers David

  • Rahul JotForm Support
    Replied on April 3, 2023 at 3:10 AM

    Hi David,

    Thanks for getting back to us. Unfortunately, you can't split the data collected from form in a single submission to a new row. Each field on the form will send the data to the related column in the google sheet. For example, if you are using a Configurable list to collect 20 rows in a single submission, it will be collected in a single row. You'll need to manage it at your end.

    While the feature you’re looking for isn’t currently available, rest assured we’re always working to add new services and features to Jotform. 

    I've gone ahead and escalated your request to our Developer Team. When or if it's developed depends on their workload, how viable it is, and how many other users request it. If there are any updates, we’ll circle back to this thread and let you know. 

    Let us know if you have any other questions.

  • davidzuiddam
    Replied on April 3, 2023 at 4:47 AM

    Hi Rahul,

    Thanks

    :)

    Cheers David

  • admin_Admin_admin294
    Replied on April 8, 2023 at 2:07 PM

    I don't see that this has been resolved!


  • Vanessa JotForm Support
    Replied on April 8, 2023 at 10:57 PM

    Hi admin_Admin_admin294,

    It seems that you already opened a thread and my colleague already replied, here's the link to open that support forum thread.

    If you want to follow up, please post your reply on that thread to prevent confusion.