Knarr's Google Sheets connector uses OAuth 2.0. This means that when you set up Knarr to pull data from your Google account, Google will ask you to authorize Knarr to access your spreadsheets, as seen in the image below.
For reference, we only request the /auth/spreadsheets.readonly scope from Google, so we're not looking to get access to your entire Google Drive, only to read your spreadsheets from links you provide to us.
Once you've allowed a connection to your Google spreadsheets, you can select that connection in the Data Source dropdown. This will prompt a URL box to appear, into which you can copy and paste the URL of your Google Sheet.
Upon pasting a valid link, a "schema" of the Sheet will appear in the Tables section. This allows you to see a list of the tabs (or tables) you have available in your Sheet. Clicking the gray [SQL ->] button will insert all of the fields into the SQL input box to the right. From here, you can manipulate the fields any way you like using standard SQL.
If you're not familiar with SQL, W3 Schools is a fantastic free resource that you can use to learn about it. Their intro to SQL starts here: https://www.w3schools.com/sql/sql_intro.asp
Note that since you can only load one table at a time, clicking the SQL -> button will overwrite your current SQL code. If this happens, you can click into the SQL box and press Ctrl + Z on your keyboard to reverse the changes.
Click Generate Preview to load a sample set of data so you can see your query results. Keep in mind that if you have a large dataset, it may take a few seconds or even up to a couple of minutes to preview the data, so be patient if it's loading.
If your schema is not appearing or is not complete, make sure of the following things:
- The Sheet you are pasting in can be accessed by the account you authorized, e.g. if you authorized your work email but the sheet you're trying to use is a personal Sheet, it will not work.
- Your G Suite administrator is not blocking connections from third party applications - this shouldn't be the case if you were able to connect in the first place, but it does happen sometimes.
- Your tabs/tables within your Sheet aren't hidden
Tips & Tricks
- You can create connections to multiple Google accounts.
- If you're SQL-savvy, you can join data across tabs within a single Sheet, giving you a lot of flexibility to manipulate the data and perform ETL on it prior to bringing it into Knarr.