If someone at your company does this with their personal account and then leaves, that account will be deactivated and everything will break.
So yeah, use a service account.
????Once you do that you’ll get a json file with the necessary credentials that you will use to authenticate the api call.
Save that to wherever you want to store it.
I’ll use one of the public datasets for this example and we’ll send a query to the api, get back the data and put it into a df like last time.
OK so now you have the data from either an api or a database and it’s set up to get the latest day’s data.
It’s in a df so you can do whatever transformations you need to do.
Next you need to set up the sheets api so you can put it there.
First step is to create a service account that will be authorized to read/write to a sheet via the api.
If you already have one from using BQ then you can use that if it’s part of the same project.
You just need to enable the sheets api for that service account.
For those who don’t though, below are the instructions on how to set one up for the sheets api.
Create a service account by going to https://console.
developers.
google.
com/Create or select a project.
Ours will be called ‘Analytics’Enable the google sheets api4.
Search for and select the sheets api and enable it5.
Search for ‘credentials’ then select ‘Create credentials’ then select ‘Service account key’6.
You will then be directed to the below page where you have to give your service account a name (we’ll call ours sheets-api-reporting) and you have to select the roles.
In the project section we will select the editor role which will allow us to do what we need to (you never want to give a service account more permissions than it needs).
Leave the ‘Key type’ as json and click ‘Create’.
A json file will automatically download to your computer with the credentials you need.
Rename it and put it in your desired folder.
Create a new google sheet and grant the service account e-mail address access to it.
The service account email address can be found in the json file in the ‘client_email’ field.
You can now read and write data to that sheet with the api!!!!Photo by Ian Stauffer on UnsplashNow is a good time to test that you just set up everything correctly.
The below test script will add 1, 2, 3, 4, 5 to columns A1:E1.
You have to install the required libraries, add the scopes for google sheets, define the path to the credentials file, add the spreadsheet id and the sheet name.
You can get the spreadsheet id from the url of the sheet you created:In terms of how to add the data in the correct format etc.
it’s all in the api reference: https://developers.
google.
com/sheets/api/reference/rest/ but you basically have to pass in a range of columns and rows where the data will live and then pass in the data as an array of arrays.
Putting It All TogetherI know that seems like a lot but most of it is in the initial set up and the ROI will be worth it and you’ll be more productive in the long run.
????The below script will grab all of the ‘artificial intelligence’ articles from the news api and append them to a google sheet for yesterday.
In terms of running this automatically you have a few options.
You can run it automatically with windows task scheduler, set up a cron job if you’re on Linux or you can run it as a cloud function on GCP/lambda on AWS.
Running it as a cloud function/lambda has the benefit of being serverless and it will run even if you’re computer is off/crashes.
GCP has a free tier where they give you $300 for 12 months.
I have about 8 cloud functions running everyday and I don’t know the exact cost but it’s maybe a few dollars a month at the most so you could set up a lot for free.
.