Integrate Google Sheet with Jotform

  • Profile Image
    Jaf3778
    Asked on July 24, 2017 at 10:55 AM
    Hello,
     
    I would like to integrate a Google sheet with a Jotform page in the following way:
     
    The Jotform page should display column content from a google sheet that will be shuffled automatically every 24 hours. There should also be a "Shuffle Button" under the display to shuffle manually if needed. Only 5 of the items in the column should be displayed.
     
    This Jotform page should also be able to collect additional information which would be sent into the columns in that same google sheet or another google sheet in the same folder.
     
    Let me attempt to provide an example: I now have a Google sheet with the name of a different muscle group in each header, row 1, columns 1 through 10. Chest, triceps, biceps, etc
     
    Under each of those headers, I have a list of different exercises that I can do to work on each of those muscle groups (headers). Bench press, push- ups, pull- ups, triceps kickbacks (column)
     
    I would like the Jotform page to display a random/shuffled order of those exercises in that column shuffled automatically every 24 hours. There should also be a "Shuffle Button" under the display to shuffle manually if needed. Only 5 of the items in the column should be displayed.
     
    I would like to be able to duplicate this same process for any of the columns in any google sheet on any another Jotform page.
     
    Other Options
     
    The display should have 3 options next to each of the 5 items displayed. One, mark the item as "completed" (which will go to the bottom of the shuffle). Two, "hide this item" from the shuffle (which will hide but them re- enter the shuffle after 40 days) Three, "Permanently Delete" this item from the list (which will remove it from the sheet.
     
    The Jotform page should also have an option for me to add a new exercise or header (muscle group) to any one of my sheets/ columns in that folder.
     
    The best way to describe this project is making a Jotform Dashboard to display sheet data & choose whether to mark complete, hide for 40 days or remove it permanently. Also, the form should be able to collect new information & be directed to any sheet column in a folder chosen from a drop down selection. Thank you for any help that you can provide, James
  • Profile Image
    Chriistian
    Answered on July 24, 2017 at 03:26 PM

    From what I understand, your requirement will mostly involve fetching data from your existing Google Spreadsheet and sending back some data to the same spreadsheet. Unfortunately, our Google Spreadsheet integration is a one-way integration which can only send data from your form to your spreadsheet. Fetching is not yet possible. Aside from that, most of the logic you have given will require some complex programming and server setup. I will try to give more details below.

     

    The Jotform page should display column content from a google sheet.

    We currently do not support this. The reason being is that JotForm's main feature is to collect data, not the reverse (i.e. to show data). A workaround would be to convert your spreadsheet into a CSV file and use our Import App to display the data. But it is not real-time as you require.

     

     

    I would like the Jotform page to display a random/shuffled order of those exercises in that column shuffled automatically every 24 hours.

    - Shuffling/Randomizing page content requires some programming. Scheduling it to be done every 24 hours will require you to set up a cron or scheduled job on your server.

     

    There are some of your requirements that can be done with JotForm, though.

     

    This Jotform page should also be able to collect additional information which would be sent into the columns in that same google sheet or another google sheet.

    Our Google Sheet integration feature will let you send data submission to your spreadsheet.

     

    I would like to be able to duplicate this same process for any of the columns in any google sheet on any another Jotform page.

    - Once you have created a form in JotForm, you can easily duplicate it by cloning it.

     

    Let us know if you need further help.

  • Profile Image
    iondjp
    Answered on July 26, 2017 at 03:08 PM

    I would like to add to this discussion regarding the Import App. In my opinion the import app needs a few adjustments to make it truly worthwhile. The fact that JotForm has no way to do batch edits on submission fields, and since there is no two-way or inward syncing with Google Sheets or any other external data, makes this import feature a 'last chance' hope for many serious forms based applications.

    Here's what I mean....

    First, here are the steps to setup and replicate:

    1. Clone a form - (for description purposes here, let's call them Form1 and Clone of Form1)

    2. Open Submissions for Form1

    3. Use 'Download As CSV to get a local copy of the data in my submissions of Form1

    4. Launch Import app (http://import.jotform.io/)

    5. When the CSV header information is displayed, copy it

    6. Open the local version of the CSV and paste the Import App Header information over the existing headers in the file. Technically, you can make the test a little easier by simply pasting the Import App header information at the beginning of the CSV file. If you do that, the first record that is imported will be the names of the original headers. You could then simply delete that record after the import.

    7. Save and Close the CSV.

    8. Use the Import App to import the newly modified CSV.

    Here are my observations....

    1. I had 77 submissions in my original csv but the Import App reported it imported 82. Investigation of this revealed that some of the lines of data (i.e. the records) were wrapped. Likely due to special characters in the data throwing things off. In any case, this is not good and is certainly not easy for less technical users to identify or fix.

    2. The Import App indicated I had imported 174 fields, but the CSV I had downloaded of my Form1 has 237 field headers. This is quite unnerving when you have hundreds of fields (a.k.a - questions). However, when I perform the download of the data again and open the excel or CSV, it does appear to be in tact. I suspect that many of these are non-data fields that are created when you add dividers and sections. In any case, these should not be a part of the data table. They make things more complicated in a number of areas.

    Now, in my humble opinion...

    a.

    Steps 5, 6, and 7 of the Setup and replicate instructions are cumbersome and error prone. At present they are mandatory for successful import. If you do not change the header, the Import App will spit out errors. On the other hand, the export or the download function should deliver a CSV file that is ready made to be imported by the Import App. IOW, the headers should be the same. I should not have to cut and paste.

    b.

    the Import App has no way of allowing me to replace existing data or duplicating. Repeating my import doubles all records. I'd like to have the option to replace existing records, merge existing records, duplicate records, or add only unique records. This should not be too hard to do since each submission has a unique ID number.

    Just my 2 cents, adjusted for inflation.

     

     

     

     

  • Profile Image
    Chriistian
    Answered on July 26, 2017 at 03:26 PM

    Thank you for your suggestions. They are great! I had to create 2 separate threads for them so we can deal with them properly.

    https://www.jotform.com/answers/1209220

    https://www.jotform.com/answers/1209223

     

  • Profile Image
    johny 
    Answered on August 06, 2017 at 06:00 AM