This is place holder for documentation on import Google Sheets into a dataset collection.
dataset COLLECTION_NAME import-gsheet SHEET_ID SHEET_NAME CELL_RANGE COL_NO_FOR_ID
dataset supports importing data from a single sheet at a time from a Google Sheets document. To do this you need to beable to authenticate with the Google Sheets v4 API and an account with the permissions allowing it to read the Google Sheets document. Google Sheets like Excel workbooks include multiple talbes in a single document. This is usually called a sheet. When importing a Google Sheet into a dataset collection the collection needs to exist and you need to identity the source of the key. If none is provided the key will be created as the row number of each JSON document constructed from the column header and cell value. This is problematic if someome sorts the sheet differently and then re-imports the data into the collection. So usually you want to explicitly set the column that will be used as as the record key in the collection. That way you can re-import the sheet’s data into your collection and replacing the stale data.
In this example we’re using the example Google Sheet from the Golang Google Sheets API v4 Quickstart. You’ll first need to have created a client_secret.json file as described in the Step 1 of the Google Cloud SDK docs and placed it in etc/client_secret.json. Our collection name is “DemoStudentList.ds”.
export GOOGLE_CLIENT_SECRET_JSON="etc/client_secret.json"
dataset DemoStudentList.ds init
dataset DemoStudentList.ds import-gsheet "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms" "Class Data" "A1:Z" 1
dataset DemoStudentList.ds keys | while read KY; do dataset DemoStudentList.ds read "${KY}"; done
In this example we’ve used the row number as the ID for the JSON document created. This isn’t ideal in production as someone may re-sort the spreadsheet thus changing the number relationship between the row number and the document in your dataset collection.
In this version we’ve not used the first row as field names in the JSON record. How does it look different? What does “-use-header-row=false” mean? Why is the range different?
dataset -use-header-row=false DemoStudentList.ds import-gsheet "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms" "Class Data" "A2:Z" 1
dataset DemoStudentList.ds keys | while read KY; do dataset DemoStudentList.ds read "${KY}"; done
Related topics: dotpath, export-csv, import-csv, and export-gsheet