S
S
SheetDream
Search…
Google Sheets
You can connect Google Sheets as a data source in SheetDream, creating an API directly from your sheet. Here is what the Google Sheets screen will look like the first time that you visit it.
To start, you will need to go to Google Sheets and adjust the share permissions for the particular worksheet you want to create an API from. For instance, here is a restaurant menu worksheet:
Click "Share" in Google Sheets on the top right. A new dialog will popup, asking if you'd like to share with specific email addresses, or for anyone with the link.
You can adjust the permissions as you see fit. If you decide to allow anyone some level of access with just a link (SheetDream included), you can do so with the bottom panel.
If you prefer that only SheetDream be given access to read the spreadsheet, you can input the following email address in the top panel and give the appropriate permissions. Take note: if you enable writeback (discussed below) but fail to give SheetDream editor level permissions, synchronization will fail and will most likely cause errors.
This is SheetDream's service account email address.
After you've got your sharing permissions setup for the Google sheet, copy the URL.
After that, there are a number of fields that can be filled in. A description of these fields is below:
Field
Description
Sheet URL
The URL to your Google Sheet. Copy & paste the URL in your browser from your Google Sheet tab here.
Sheet Name
The name of the sheet (otherwise known as a tab within your Google Sheet, on the bottom left) you want to create an API from.
Destination Table
SheetDream caches your spreadsheet data in the form of Tables, and this tells SheetDream what you want your table to be called. The API object used to access your spreadsheet will be the lowercase form of this value.
Allow SheetDream To Update This Sheet
Tells SheetDream you want to allow API callers to be able to make changes to the spreadsheet via POST, PUT, PATCH, and DELETE API calls. By enabling this, there's a minor possibility that you're modifying the same record that SheetDream is modifying, resulting in a scenario where it may overwrite the changes you just made.
Write API ID Back To Sheet
In some instances, you may want SheetDream to write the API's resource ID for each particular record back to your sheet. If you check this, you can specify a column that SheetDream should write this value to.
Active
Turns synchronization on and off. Activated synchronizations will have a dark blue header, deactivated synchronizations will have their title/header greyed out.
If you want to add more than one Google Sheet to synchronize, you can do so by clicking "Add Sheet From Google Sheets".
After you have made any changes on this screen, you will see a "Save Changes" button appear on the bottom right. Make sure to click this before proceeding to a different tab.

How To Structure Your Google Sheets

SheetDream won't be able to recognize just any sheet and build an API from it. Your sheets will need to follow a specific format so that SheetDream can discover the header row successfully (where it can deduce your column names) and the first row of data.
There's basically three different rules: your data must start on column A (the first column in the sheet), and the first row (row 1) must be the header row containing column names, and if you enable writeback, you need to have a unique ID row. Here's an example spreadsheet:
So A1 will be the first column in your table. B1 will be the second column, and so on.
A2 will be the start of the first row of data.
Our ID row is called "ID", and it's in the very beginning. The ID row is important, because it tells SheetDream how each row is uniquely identified, which helps ensure the integrity of your spreadsheet(s) when users start making POST/PATCH/DELETE/PUT calls to your API. For simplicity in interacting with your API, if you create a new record via a POST to your API and do not provide the ID row, SheetDream will automatically populate an ID for you (it will use its own internal ID for the record).
Generally speaking, it's usually best practice to use a form of ID that is guaranteed to be unique like a GUID. If you use integers and you aren't careful, you may accidentally introduce multiple records with the same ID which will cause errors. In that scenario, you'd need to perform a hard reset in SheetDream by purging the table from the cache on the Data tab.

Data Merging Considerations

As with Airtable, unless you're running some sort of sophisticated data merging algorithm, when syncing data between multiple data stores (such as SheetDream and Google Sheets), one of those data stores needs to be considered the source of truth.
When you hook SheetDream up to a Google Sheet and give it permission to update, SheetDream becomes the source of truth for that particular dataset.
What this means is: if you were to make a modification to a specific row (either changing some columns, or deleting the row) on the Google Sheets side: but a user of your API happens to make a change to that exact same row at the same time, there's a possibility any changes you made will get overwritten by SheetDream.
This is a deliberate design consideration to ensure that you can give your users the best possible experience from the API side.
It's for this reason it's generally a best practice to give SheetDream its own Google Sheet where it will be making updates to (and you will be rarely touching), and you then use Google Sheet's VLOOKUP feature to pull said data into the worksheet you'll actively be working in.

Important Notes and Restrictions

The main restriction you may run into is that you can only have one Google Sheet syncing to a specific table at a time. This extends to Airtable as well: you cannot have both a Google Sheet and an Airtable syncing to the same table in SheetDream.
The reason for this is to prevent very complicated data integrity issues. Two-way synchronization between your API and Google Sheets is difficult enough, three-way synchronization poses many issues that SheetDream does not want to handle.
If you feel that there is a reason that you need to do something like this, more than likely, you will need to rethink the design of your application that you are building within SheetDream. There is more than likely a much cleaner way to do what it is that you are trying to do.

SheetDream / Google Sheets Limitations

The major limitation you will run into is how dates are handled. The general recommendation is that if you're inputting a date into a cell in Google Sheets, you should utilize the standardized ISO UTC format:
1
2021-08-29T13:03:48Z
Copied!
This is the date for August 29th, 2021 at 9AM pacific time but in the UTC timezone (1pm or 13:03:48 exactly).
This is the format of how SheetDream processes dates internally.
UTC stands for coordinated universal time, and is generally the time zone APIs and different computing systems use to communicate with one another. You will notice almost all APIs tend to strictly communicate in UTC time for ease of use and predictability.
If you choose to represent your dates in a different time zone within your sheet, SheetDream will interpret your local time dates as UTC time. While this may not be a big issue if you're simply presenting dates verbatim on screen in an embedded sheet on your website, if you have any date logic at all (such as comparing the current date to a date within a value in your sheet) it could lead to unforeseen results.
Don't buck the trend! Follow the path laid out in front of you by millions of API developers for years now. Use ISO-8601 format to represent your dates.
If you want to represent the date differently within your spreadsheet for whatever reason (e.g. readability), then have a secondary date field that uses the following formula to calculate the date from the ISO 8601 formatted date:
1
=DATEVALUE(MID(A1,1,10)) + TIMEVALUE(MID(A1,12,8))
Copied!
If your A1 cell was populated with:
1
2021-08-29T13:03:48Z
Copied!
The above formula will extract the date value and the time value from the ISO string and construct a Google Sheets date object for you. From there, you can format the resulting date however you want using the standard Google Sheets formatting functions.
Another thing to take note of: if you change the fundamental type for a particular column in your spreadsheet (e.g. you repurpose an existing column to represent a date instead of a number) then you need to click "Purge From Cache" on the Table Preview tab in SheetDream. This is because SheetDream's cache still has your old data: and when it sees your new data it may become confused about the type of the column in question. A good general rule of thumb is that if you make a structural change to your spreadsheet (moving columns around, renaming columns, changing column types) then you should click Purge From Cache and clear out SheetDream's cache.
Google Sheets sync will generally take around 3 to 10 minutes for data to refresh. This means that it will take 3-10 minutes before you notice any API-made modifications reflecting in Google Sheets, or new Google Sheets changes reflecting in the API.
If you would like faster data refresh times, please contact us about running private servers on our Premium tier.