Allowing users to pull data from Google Sheets

Need help APIs
1

I am building an inventory app and want to allow users to access their own Google Sheets documents so that they don’t have to re-enter all of their inventory manually. Since I cannot create data types manually, I would have a template available for people to use so there wouldn’t be any conflicts when importing data. That said, how do I create this functionality? I have been reading up on using API Connector to get the job done, but it’s all very confusing, and there’s really no information currently out there to get me rolling. Should I be using the Google Drive API instead of the Google Sheets API? Let me know if you need any more clarification.

Thanks!

2

Hi @thomas.r.lemon,

One way to achieve this:

  1. Create a data structure that supports the way you want inventory to be managed in the app.
  2. Create a CSV template with this structure in mind (like you said). Column headers should match your field names.
  3. Give your users the ability to download this CSV. You can use a link element that goes the CSV’s hosted URL. Use a file uploader element to quickly upload the file and have it hosted in your app. You’ll see the URL as soon as you upload.
  4. Ask your users to fill in the template with their data. For many cases, they’d copy and paste from their current sheets or create a copy of their sheets and rename their column headers to match your template exactly. The important thing here is that the column headers match your data type fields.
  5. Use the “Upload data as CSV” action and Bubble will map the column headers to your fields and create new records for every row in the sheet.

This is actually a more straight forward way of importing your users’ data because with the API route, you’re assuming every user has the exact same sheet setup, which they won’t… (unless you know that they do??)

This should move you forward! We actually have a full lesson on this exact workflow in our VIP Membership if you want to check it out!

Cheers,
Gaby

Coaching No Code Apps
Join our Facebook group for insider access to no-code development
Get professional development services
Enroll in expert-led courses and products

3

Hi @romanmg,

Thanks for your response. I have used sites before that allow me to sign into Google Drive to retrieve my data. I want that functionality without having to pay for the upgrade to upload/download CSV files. Otherwise, I would have had this figured out already.

If I could have the users log in to their Google accounts through my website, would I still need to use the upload/download CSV functionality in Bubble? It seems like I should be able to use an API. I’m trying to do this without spending hundreds of dollars for a solution.

Also, what do you mean by same exact sheet setup? If I’m not mistaken, that’s the entire purpose of the template CSV.

Thanks,
Thomas

4

Hi Thomas,

Sure, happy to clarify.

Yes, you can use Bubble’s API Connector (free plugin) to create an integration with the Google Sheets API. This method will give you the most control over the connection, but it’s also the most advanced route since you’ll have to handle the OAuth2 authentication yourself.

I believe there are one or more 3rd-party-published plugins that integrate specifically with Google Sheets as well to save you some time. It’ll depend on whether those more ready-made plugins have everything to support your needs.

Connecting to GSheets via OAuth2 will mean the user will be directed to Google to Grant access to their Sheets data and then directed back to your app. You’d use API calls to read their sheets and create workflows to save cell values to your database. This part is the trickiest if your users’ own sheets are all structured differently. You’d have to know which of their columns/rows are which to map to your own table(s). This is what I meant by assuming they all have the same setup.

To avoid making that assumption, you could use the API to display their own sheet data back to the user in your app so that they can map it themselves… You’d just need to create that interface and the workflows to make it happen.

The API gives you the most control, but it’s a lot to work out, which is why I suggested starting with a template CSV that your users can download, add in their data, export and then upload to potentially give you more reliable results.

No, with the API Connector, you wouldn’t be using the upload/download actions.

Of course, all this is said with a lot of assumptions being made about your app. I don’t know what kind (or size) of data your users have, your app’s data structure, or anything else. So much of this can be tackled many different ways and I’m not saying any one method is right or wrong - it just depends on what you’re building - but hopefully this helps point you in a direction.

As an aside, if you’re building a business app, you’ll need to get on a paid plan eventually to have it on a custom domain, remove the Bubble banner, get increased server capacity, use API Workflows (different from API Connector, but will very likely be needed at some point for scheduled flows and/or recurring flows and/or batch processing), and other beneficial features. If you need any of those, then the upgrade will also include access to the upload/download actions.

Hope this helps!

1 Like