Use Google Sheets as a "database" for simple or small size applications
References
Thanks to https://www.alec.fyi/set-up-google-sheets-apis-and-treat-sheets-like-a-database.html
Setup GCP
Reuse or create a project
- Go to https://console.developers.google.com/ and use an existing project
- Or you can create a new project by going to https://console.developers.google.com/projectcreate
Enable the needed APIs
- Search for the "Google Sheets API" by using the search box at the top of the page
- Enable the API
- Search for the "Google Drive API" by using the search box at the top of the page
- Enable the API
Create a service account to enable server 2 server communication
- Click the hamburger menu -> "APIs and Services" -> "Credentials"
- Click "+CREATE CREDENTIALS" and select "Service account"
- Enter a service account, click "CREATE" and "DONE"
- You'll end up on the credentials screen, click the newly created service account at the bottom of the page
- Click "ADD KEY" -> "Create new key". Make sure to save the json on your workstation
Share the sheet to your service account
In Google Sheets, open the sheet you want to use as a "database".
- Click "Share", in the share dialog, you can simply put the service account email address and click done.
Setup Node.js
On your workstation, run the following:
yarn init;
yarn add dotenv google-spreadsheet;
Setup the necessary environment variables
touch .env
In this .env file, you will need to put the following variables:
GOOGLE_SHEET_ID="your google sheet id, this is the long id in the sheets URL when you have it open in your browser"
GOOGLE_SERVICE_ACCOUNT_EMAIL="client_email value from your key json"
GOOGLE_PRIVATE_KEY="private_key value from your key json"
Copy the code below into an index.js file, afterwards simply run node index.js
. It should output the title of your doc.
Go forth and call them APIs
You can build this into an application or a server; Google Sheets is now your API oyster, have fun!