Step 1 : Create a Google Sheets API key
You will need to create a Google Sheets API key that allows the transmission of the data between Google Sheets and Grafana.
Login to your Google account using this URL: https://console.cloud.google.com/
Click on the Main menu icon (☰) on top right and navigate to the API & Services and select Enabled APIs & services.
Click on the CREATE PROJECT button.
After that, you will be asked to give a Project name. You can give it any (in the screenshot, we have given it as google-sheets). After that, click on the CREATE button.
Note : You can also provide organization name , but it is not mandatory
The page will refresh, and you will see a top search bar. Type Google Sheets API, and Google Drive API (required for spreadsheet access)
you will see the result in the drop-down list. Click on it.
Then you will see the Google Sheets API product details. Click on the ENABLE button.
Click on the Credentials option on the left menu, and then create the Service account by clicking on the CREATE CREDENTIALS button.
Now Enter the service account name and copy the email address
Now click on edit service account and edit the service account
Now click on keys to create a new json key for authentication
Now click on add key and create new key
JSON will be default selected so don’t make any changes in this and click on create button
Now click on IAM option from left box
Now click on grant access option and select the email id which we have created in our previous steps
Now add principals and add user email which we have created previously and click on assign roles
Now select IAM OAuth Client Viewer and click on save
Now copy the email and paste it to google sheets
Now add email and click on send make sure uncheck the Notify people
Install and Configure the Google Sheets data source plugin
The final step is to install and configure the Google Sheets data source plugin in Grafana UI by following the given instructions:
Login to the Grafana UI as a server administrator.
Click Administration > Plugins and data > Plugins in the side navigation menu to view all plugins.
Type Google Sheets in the Search box.
Click the All in the State filter option.
Click the plugin’s logo.
Click Install.
Once the plugin is installed then:
Browse to the Connections menu and then click on the Add new Connection.
Type Google Sheets in the Search box.
Click on Add new data source button, located on the top right.
Go to its settings tab and at the bottom, you will find the Authentication section.
Select Google JWT and upload the downloaded file
Now go back to dashboards and click on new dashboard and choose the google sheets as data source.
Click on enter spreadsheetID and enter the spreadsheetID
Copy the unique spread sheet id and paste it here
(Note: before copy the unique id make sure you have change access to anyone with the link)
Now paste the unique id and click on enter then save and apply
Final Outcome:-
