Connecting to Google Sheets allows you to pull in and manipulate spreadsheet data like you would a database. This allows you to:
- Easily view and combine Sheets data with data from other sources (like MySQL or Salesforce)
- Build a frontend to view and update Sheets in a controlled manner, with column-by-column user permissions
How to connect
Sign into your Google account and give Internal permissions to access your Sheets. Then select the workbook that you want to connect.
What happens when you connect
When you connect a Google Sheets workbook, Internal automatically generates:
- A list function for each Sheet within the workbook. So if your workbook has two Sheets, "Users" and "Companies", Internal will generate a "list Users records" function and a "list Companies records" function. List functions read data from your tables and allow you to display that data in components - think of these as prebuilt SQL queries, so you don't have to write queries for everything.
- An insert, update, and delete function for each Sheet in the workbook. These functions will allow you to manipulate the data within each.
Working with Google Sheets
As Google Sheets are easily editable, special care must be taken to prevent conflicts with changes from Internal.
- If you plan to write back to a Google Sheet from Internal, we recommend only allowing editing, sorting, and creating new records from Internal. Internal uses the row number to identify the record to be updated. As a result, if someone edits, sorts, or adds data directly to a Google Sheet while another user is trying to edit the same Sheet from Internal, there could be unintended changes made to your data.
- If you do want to add additional columns of data in the Sheet, we recommend you add it as the right-most / newest column, and then syncing your data to import the new field(s). This will help prevent conflicts.
Note: Google Sheets and "Everything is a String"
Due to the nature of Google Sheets' formatting, all of the data sent through our adapters comes back as strings. As a result, any comparator operator (including Sort and Filter functions) which uses integers or floats will not work. Instead, we recommend sorting/filtering on alphabetical columns.
If you must sort by a numeral, we recommend using a different data source.