How to Upload a Google Sheet as Reference Data

Have more questions? Submit a request

By integrating your GoCanvas account with Google Sheets, you can use your Google spreadsheets as reference data in GoCanvas.

Google Sheets Integration

Reference Data is a handy automation tool that uses data uploaded to your account to automatically populate fields, saving time and eliminating manual entry. This also ensures that your users have the most up to date information that you provide them. 

In order to use your Google Sheets files as reference data, you will need to authenticate your Google Account with GoCanvas. Your authorized credentials will be kept confidential. GoCanvas will not have access to your Google password. We will only know that you have provided us authorization to have read only access your Google Sheets files and data.

GoCanvas must be provided access to certain data to import your files.

What is GoCanvas is requesting?

To see and download all your Google Drive files and to view your Google Spreadsheets.

Why is GoCanvas requesting this data?

GoCanvas must have access to see and download all of your Google Drive files so that we can display the files that are compatible with reference data. GoCanvas must have access to view your Google Spreadsheets in order to import the data from your selected spreadsheet into GoCanvas as reference data.

Authorize and Add a Google Sheet

Authenticating your account is an integrated step within adding your first file from Google Sheets.

  1. Navigate to the Reference Data & Images page. Reference Data from Google Sheets can only be added from this page.
  2. Select Add Reference Data.RD&I_Google Sheets Integration_Add Reference Data.png
  3. Import your Google Sheet by selecting Import from Google Sheets and Next.RD&I_Google Sheets Integration_Import from Google Sheets.png
  4. Select Authorize to start this process.RD&I_Google Sheets Integration_Authorize Google Sheets.png
  5. Sign into the Google account that you would like to import reference data from, and then select Allow.RD&I_Google Sheets Integration_Authorize Google Sheets_Sign In.pngRD&I_Google Sheets Integration_Authorize Google Sheets_Allow Connection.png
  6. When the authorization is complete, a pop-up will confirm that it was successful. Select Next to continue to the next step.RD&I_Google Sheets Integration_Authorize Google Sheets_Authorization Successful.png
  7. Select the file that will be you reference data. You'll be able to select any Google Sheets file that has been shared with you. Files up to 20MB are supported. This screen also has a Search bar to quickly find files or the option to select Change Account if the file you are looking for is not in the current account.
    RD&I_Google Sheets Integration_Select a file.png 
  8. Additionally, the optional step of selecting the sheet where the data is located will only occur if the file selected has more than one sheet.RD&I_Google Sheets Integration_Select a sheet.png

Note

This integration does work with Team Drives, with some caveats. Google Team Drives do not automatically appear in Sheets. In order for the file to appear as a choice, you must share the file to yourself from the shared drive.

Essentially, any file that you have in your own Sheets account is accessible to this integration, regardless of who originated the file. However, files from a shared drive will not show the owner when choosing the file. 

GoCanvas Tip

Since it is possible to upload multiple Sheets from the same Google spreadsheet as separate reference data files, utilizing this functionality is an easy way to keep your related datasets organized and easily accessible to edit in your Drive.

Final Upload Settings

Editing the file name and description is an optional step that we recommend for keeping your files organized. Several other optional steps will occur in this last page before the file is uploaded to your account.

  • The name of the file will automatically populate from Google but it can be changed. Renaming the file in GoCanvas:
    • DOES NOT change the name in Google Sheets,
    • DOES NOT impact our ability to refresh data from Google Sheets.
  • Next, you may want to add a description about the file to quickly indicate what is in the file;
  • Check the box to Divide by User Groups, only if the file is designed for that;
  • If the file has more than 15 columns, you will be asked if you want to proceed with the upload of only the first 15 columns or if you want to cancel the upload.

RD&I_Google Sheets Integration_Upload Data.png

After saving, you will see all uploaded files on the Reference Data & Images page. Icons denoting whether the file is from Google Sheets or a CSV file will be visible at the start of each file row.

RD&I_Icons Annotated.png

If there are any changes to the Google Sheet, simply select the file name on the Reference Data page, then select Re-Sync Data in the upper right corner to automatically update the file in GoCanvas.

RD&I_Google Sheets Integration_Resync Data.png

Note

If the file is moved within Google Drive, the file name in Google Sheets is changed, or authentication with Google Sheets has expired, you may need to reauthorize and reselect the needed file. 

Articles in this section

Was this article helpful?
0 out of 1 found this helpful
Share

Comments

22 comments

Please sign in to leave a comment.

  • Thanks Lauren. this seems to be a very positive update. my question is, since we can now connect and integrate GoCanvas to Google sheets, will we also be able to have submissions into Google sheet, instead of having to use Zapier or other third party connections?

    0
  • Are there plans for the ref data to be updated directly from google sheets upon user sync? That would make this extremely useful. 

    1
  • Hey ya'll!

    CJ Friedman , that is an interesting question! As of now, Zapier is the best bet for simple-trigger integrations. I can raise the question with product about including Google Sheets in our Standard Integration options! I don't believe we have a Community post for this feature request and we would love to see the response from other GoCanvas users. If you have a moment, we would be very appreciative you add this request to the Community!

    Kenton Linthout , that is another great question! There is discussion about including that as an update but there is not solid timeline. Obviously, more interest means more possibility that they will explore that update. However, now that this integration is available, creating a Zap to automatically update reference data is an option that you can explore on your own or with the help of GoCanvas ProServ.

    (Edited )
    0
  • So we still have to press a sync button to update the data?

     

    0
  • Hey Salvatore!

    Yes, you will still need to hit re-sync but this should be exponentially easier than all the steps necessary to update a CSV file now. We are on the lookout for feedback on automatic syncing and would be happy to consider building it out faster if the interest and need is there!

    1
  • If this isn't triggered by a user sync and you have to manually go and re-sync sorry but all you're really doing is saving a little bit of time.  If it's user sync and you Zap every submission back to the same sheets you can make feedback loops with implications that are truly mind boggling.

    Use that Ref data to control conditional fields you could make the most complicated and dynamic workflow between users you could imagine.  You could actually build mutli user puzzles and games that's how much more powerful the platform suddenly becomes. 

    We run through lists of vehicles, tech's constantly select the wrong one and if it's already been done it just overwrites the data in google sheets and makes a mess that is difficult to sort out. Imagine 20,000 vehicles in sheets with 5% fat fingered and showing the wrong data. If I can feedback to the tech that the one selected has already been marked complete then have them verify in app if they're correct or the first submission was, it would completely eliminate this problem and you could self correct it.  I'm pretty sure that's barely scratching the surface of what you could really do. 

     

    1
  • Hey Kenton,

    I'm not sure I understand the first part of your comment. When I say re-sync, I mean when changes are made to the Google Sheet, those changes will be added to the reference data when the re-sync button is clicked within the Reference Data & Images page. Users always have to sync their device when new reference data is available, that did not change. This integration eliminates the steps of downloading the file, or hunting it down on your desktop, making changes, editing the file in GoCanvas, and re-uploading the file. This is much faster and allows you to make all changes to your reference data without leaving your browser.

    And just to make sure it is clear for others who may read this comment, submission data does not update the Google Sheets file with this integration, but a Zap can be created to mimic this action, or what we call dynamic reference data. The automatic sync that we are considering building out would simply eliminate the step where you click the re-sync button when you update your reference data in Sheets.

    With you second point I agree! This does allow for incredibly powerful use case opportunities and we look forward to hearing how our customers use this integration. You example is definitely a dynamic reference data function which can now be done with Zapier and not with a custom integration. 

     

    0
  • Say when the user synced their mobile device it went straight to google sheets to get the ref data which is a list of 10 things to do.  User does #1 records it in a submission and then we use an integration to remove item #1 from the list.  User sycs again starts a new submission and now he only has items 2-10 to do on his ref data list.   As it is now until I go in and manually sync the sheet he will always have the list of 1-10 items to do no matter how many he's already done.  It's static ref data until I sync it and then it's an updated static list. 

    You couldn't have two users working on the same list of 10 things because they both might do #1, and 3 and 7 etc.  Until I manually sync their data they're both looking at the same list of 10 for the duration.  Say though that the integration took them off the list as they completed and submitted them, then every time they sync it went straight to sheets to get the updated list of remaining items. Truly dynamic ref data then and they could both work on the list without accidentally duplicating efforts. 

     

    1
  • Thank you for clarifying, Kenton! You are correct, although this is not a dynamic integration, a dynamic reference data equivalent can be developed using Zapier.

    For those that don't want to go that route, there is always dispatching tasks to specific users so no one repeats the efforts of their colleagues. 

    0
  • Hello,

    Is there a limit to how many rows can be uploaded? I have 1349 rows in my google sheets tab and only 1004 are available. the rows below have not been included

    0
  • Hey Michael,

    Great question. There isn't necessarily a limit on rows but there is a limit on how many megabits are allowed. The limit is 20mb, but I don't think 1000+ rows would hit that limit. I am going to create a ticket out of your comment for support to help troubleshoot and escalate if necessary. 

    0
  • Hello,

    I agree that this feature will be much more useful if changes to a Google Sheet can trigger the reference data to re-sync itself rather than manually clicking the button. Even a periodic schedule (hourly, daily, weekly) would be awesome. It would be great to see that in a new update. Thank you!

    2
  • Hey Classic Installs,

    We are keeping our product team up to date on this request, they love to hear that feedback!

    0
  • Hi Lauren Sunday This feature is a great step forward, and we are using already for all our reference data. We would also love if the data could refresh automatically when the sheets are updated in Google without us having to come back in each time and click Re-Sync.

    1
  • Use case example for Pesach's comment above (that I am using in another account).

    I have a small non-profit membership database in Airtable. When information is changed or updated in the database, I have a Zapier integration to update a Google Sheet. I plan to use that Google sheet as reference data for two GoCanvas applications. When the members of my organization renew their membership dues each year (using a GoCanvas App), a Zapier integration updates my Airtable database then another Zap feeds that information back to the Google sheet being used for reference data. Once the Airtable and Google Sheet are updated with the new information, I want their information to become (conditionally available upon renewal) for a GoCanvas daily sign-in sheet. In other words, once someone submits their membership renewal, I want their information to become available (as reference data) in the daily sign up. I could have people renewing memberships almost everyday of the year. My only problem is, to complete the reference data feedback loop, I have to log into Gocanvas and manually click the reference data re-sync button.

    It would be great if the Google Sheet re-sync would trigger automatically (when updated) or at least once per day (say midnight).

    I want to build this to update itself in perpetuity. Set it and forget it. Two apps and a database working in harmonization feeding each other information. I realize I could pay for a dynamic reference data integration but I want to be able to control my database locally (and I don't want to pay GoCanvas for a custom integration because I'm trying do this for a small non-profit as cheaply as possible).

     

    2
  • Comment on my comment...  Being able to build a form (for people that don't know how to use GoCanvas) to apply updates to the Google Sheet reference data (without logging into GoCanvas and manually re-syncing).

     

    1
  • Hi Keith,

    Thank you for this detailed use case explanation! It is obvious the value that automatic sync would bring to your non-profit and many others who have commented. We have several large releases coming soon that unfortunately pushed automatic resync back but I will be sure to update everyone when I have more information.

    0
  • I experienced a minor glitch. I have more than one google account. One is a paid account for business and the other is a free account (I use for a non-profit). If I would place my reference data csv files into the google drive of my free account and tried to link them as reference data, the GoCanvas UI would not see them. If I placed those same csv files into my other paid account and linked them, the UI would see them just fine. The free account is the one I really wanted to use though. I finally figured out that the GoCanvas UI would not see the csv files in the free account until I changed the google sheet setting to "Anyone with the link can view". Just to be sure, I double checked how the google sheets were shared on both accounts. Unlike the free account, the sheets on my paid account were still visible despite the sheets not being shared. 

    0
  • Update on my previously reported glitch...  figured something out. If you drag a csv file into Google drive, you must then open it with Google sheets (so it actually coverts to a Google sheet) then it becomes available to the GC UI. When I was opening the csv files and changing the sharing to 'Anyone with a link can view', I was (inadvertently) converting it to a Google Sheet at the same time (which is what really fixed the problem). doh! lol  

    0
  • Thanks for explaining that, Keith! I'm sure there are others that will benefit from this troubleshooting advice so thank you for sharing!

    0
  • Hey everyone! 

    Wanted to give an update on automatic sync as there have been developments made toward this goal. Although the function is not solely in GoCanvas at the moment, Zapier now has the Action: 

    1. Sync Reference Data With Google Sheets - Have a Trigger cause your Reference Data to sync with Google Sheets.

    We are really excited about the possible use cases for this Action and hope that you will give it a try! Please check out our article on using GoCanvas with Zapier to learn how to build a Zap yourself or reach out to your account manager to see what our Professional Services team can build for you!

    0
  • The Google Sheets sync seems like a great step forward - with Zapier, it can sync automatically, as others were discussing. 

    Unfortunately, our organization does not use Google. A connection with Excel Online would be very helpful. 

    0