Google Sheets
Google Sheets is the online spreadsheet productivity application from Google and is part of the Google Business and Google Workspace offerings used by millions of users. This is often compared to Microsoft Excel as it offers similar and some might say more user friendly options as one of the first online spreadsheet applications in existence.
DLH.io provides this connector as a direct way to work with this spreadsheet data and the Google Sheets "files" mainly as a source to be synchronized or loaded into your cloud data warehouse destination of choice.
Pre-Requisities:
- Access to files in your Google Sheet file or files
- Use only Google Sheet documents
- Do not confuse this with XLS/X files that you may be merely viewing through the Goole Sheets interface
DataLakeHouse.io securely connects to your Google Drive account that you have access to using the security of Google authentication. Using the form in the DataLakeHouse.io portal please complete the following basic steps.
- Enter a Name or Alias for this connection, in the 'Name/Alias' field, that is unique from other connectors.
- Enter in the 'Target Schema Prefix' field, a name that will be unique in your data cloud destination where you wish to land the data.
- Provide the 'Sheet URL' field, the spreadsheet URL which you can find in the address bar of your browser.
- Select Yes/No in the 'Sync All Sheets' field.
- If you select Yes, then we will try to sync all sheets from the provided spreadsheet ignoring the hidden sheets. Similar to a single sheet, please copy the URL and ensure it has a #gid=xxx at the end of the URL.
- If you select No, then we will just sync the sheet provided in the URL. So, please look at the spreadsheet URL which consists the gid parameter. ex. if the value is like gid=12345 then 12345 is the id of specific sheet and we will pull that specific sheet only.
- for example, https://docs.google.com/spreadsheets/d/13XXp3B35fX0d77jbS60JLc2mmm2ZjWk6cSLda8U7O9I/edit#gid=109876
- Click the Authorize Your Account button which will transport you to the Google login, where you will login with your account credentials. Once your credentials are accepted and you allow DataLakeHouse.io to access your data, you will be automatically redirected back to this DataLakeHouse.io portal and you should be able to see a successful connection.
One common mistake using this connector is that a user will have opened an XLS/X file via Google Sheets. But this is NOT a Google Sheet document and cannot be used for this connector. You will receive a 400 error in the logs and monitoring section if this happens. In this case you have a few choices:
- Conver the XLS/X file to a Google Sheet
- Use the Google Drive connector instead and point to the folder where the XLSX/XLS file resides
- Use the Flat File connector and manually upload your XLS/X file (this would require you to re-upload the file every time it changes in this option)
SQL Transformations allow logic to be executed against a target connection based on a scheduled frequency or triggered event of new data on tables updated via DataLakeHouse.io (DLH.io). This especially helps when you want to control the data type set in your Target Connection since all columns are set as VARCHAR(16777216).
