Google Sheets is a powerful and the most popular tool for collecting and analyzing data. Assume if you need to collect some data from your servers, you have to go through each server and retrieve the data and insert it into your google sheets manually. So that can be a tedious task if you have hundreds of servers in your data center. The most effective way to automate tasks like that and simplify your workload is to write a shell script to access and manipulate Google Sheets directly from your terminal. In this article, I will show you how to access Google Spreadsheets using shell script and insert some values into it.
To access Google Spreadsheet using shell script, first, we need to get an idea about how to use the Google Sheets API.
Google Sheets API
The Google Sheets API allows developers to programmatically access Google Sheets and manipulate the worksheet cell data. That API can be accessed through REST API calls or client libraries in various programming languages. Before accessing the Google Sheets API, you need to have authentication with API. There are various authentication methods supported by the Google Sheets API, but in this article, we are going to use the OAuth 2.0 protocol.
Brief about OAuth 2.0
OAuth 2.0 is an industry-standard protocol for authorization and is widely used by many online services. OAuth 2.0 allows third-party applications to access a user’s Google Sheets account without requiring the user to disclose their login credentials. Instead, the user grants the application permission to access their account, and the application receives an access token that it can use to make API requests on behalf of the user. Simply that will provide the authentication and authorization to access Google Sheets API.
To use the API with authorization, you will need to create a project in the Google Cloud Console and that project will store the required credentials and other details required for the authorization process.
Create a Project in a Google Cloud Console
Go through the Create Project link and log in using your Gmail account. Then give a project name and create a new project.
Enable Google Sheets API
Once you have created a new project, you will need to enable the Google Sheets API for that project. To do this, navigate to the “APIs & Services” section from the left navigation and click on “Enable APIs and Services“.
From the API Library window, search for “Google Sheets API”. Click on the “Enable” button to enable the API for your project.
Generate OAuth Client Credentials
Once you have enabled the Google Sheets API, you will need to create credentials for your project. To do this, go to the “Credentials” section of the Google Cloud Console and click on the “Create credentials” button. Select “OAuth client ID” as the credential type.
Configure Consent Screen
To use OAuth 2.0, you need to configure an OAuth consent screen. Just click the Configure Consent Screen button and proceed with creation. During the consent screen creation process, make sure to create it as External User Type and fill in only the required details by going through 4 steps.
After creating the consent screen you have to publish it as production.
Once you are done with the consent screen configuration, move back to the Credentials section and try to create OAuth client ID again and then give a name for the client ID and create it.
You will get a prompt with created Client ID and Client Secret details. Once you obtain a client ID and client secret, you can use them to obtain an access token and a refresh token. The access token is used to authenticate each request to the Google Sheets API, while the refresh token is used to obtain a new access token when the current one expires.
Obtaining Access Token and Refresh Token
Prepare the following authorization URL with the mentioned scope details and Client ID. Make sure to replace the [YOUR CLIENT-ID] section with your Client ID and paste it on your web browser where your Gmail account is already logged in.
https://accounts.google.com/o/oauth2/auth?client_id=[YOUR CLIENT-ID]&redirect_uri=http://localhost:3000&scope=https://www.googleapis.com/auth/drive.file https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/spreadsheets.readonly https://www.googleapis.com/auth/drive.readonly https://www.googleapis.com/auth/drive&response_type=code&include_granted_scopes=true&access_type=offline&state=state_parameter_passthrough_value
Once you enter the above URL on the web browser, it will ask you to select the Google account in which you have logged in previously. Then if you come up with the following screen, click on the Advanced link and then click on go with the unsafe mode link.
Then check and grant consent to all mentioned permissions and Click Continue.
Then you will get the following screen, Copy the entire URL on the address bar and paste it to whatever text editor and extract the Authentication Code value from it.
http://localhost:3000/?state=state_parameter_passthrough_value&code=4/0AWtgzh6GEFYiNKrVi43yMDbtsy3r-f-xIOMYYtNUrW59akvG82aBCGAfsxKBGnFnvD8t_A&scope=https://www.googleapis.com/auth/drive%20https://www.googleapis.com/auth/drive.readonly%20https://www.googleapis.com/auth/drive.file%20https://www.googleapis.com/auth/spreadsheets.readonly%20https://www.googleapis.com/auth/spreadsheets
The Authentication Code is:
4/0AWtgzh6GEFYiNKrVi43yMDbtsy3r-f-xIOMYYtNUrW59akvG82aBCGAfsxKBGnFnvD8t_A
Now you can use the obtained details to invoke the Google API and get the Google OAuth Access token and Refresh Token for access to Google Sheets.
cURL Command Syntax:
curl -s \
--request POST \
--data "code=[AUTH_CODE]&client_id=[CLIENT_ID]&client_secret=[CLIENT_SECRET]&redirect_uri=http://localhost:3000&grant_type=authorization_code" \
https://accounts.google.com/o/oauth2/token
Note: Make sure to replace the [AUTH_CODE], [CLIENT_ID], and [CLIENT_SECRET] according to your application.
Example:
curl -s \
--request POST \
--data "code=4/0AWtgzh6GEFYiNKrVi43yMDbtsy3r-f-xIOMYYtNUrW59akvG82aBCGAfsxKBGnFnvD8t_A&client_id=251327740099-6667am9lnhjeffc22cequ1gdo1uchcbb.apps.googleusercontent.com&client_secret=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&redirect_uri=http://localhost:3000&grant_type=authorization_code" \
https://accounts.google.com/o/oauth2/token
Once you enter the above curl command in a terminal you will get JSON output as follows including the Google OAuth Access token and Refresh Token.
Regenerate Access Token Upon Expiration
The Google Sheets API can now be accessed using the access token generated. However, it is important to note that the access token has a validity period, and upon expiration, access to the Google Sheets API will be restricted. To overcome this limitation, the refresh token is utilized to request a new access token. Using the following curl command, you can request a new access token by providing the refresh token.
cURL Command Syntax:
curl --request POST --data "access_type=offline&refresh_token=[REFRESH_TOKEN]&client_id=[CLIENNT_ID]&client_secret=[CLIENT_SECRET]&grant_type=refresh_token" https://oauth2.googleapis.com/token
In this command, we’re sending a POST request to the OAuth2 API with our client ID, client secret, refresh token, and grant type. The refresh token is used to generate a new access token when the previous one expires. Make sure to replace [REFRESH_TOKEN], [CLIENT_ID], and [CLIENT_SECRET] with your own credentials.
Example:
curl --request POST --data "access_type=offline&refresh_token=1//0gg3yvsZe2NNVCgYIARAAGBASNwF-L9IrLiIg4_bSFdSBjz9OBrewqMMDHJWd1NNjUc_Kkw_O7MfJUXPHAJySNmK44KHD0KYy9o0&client_id=251327740099-6667am9lnhjeffc22cequ1gdo1uchcbb.apps.googleusercontent.com&client_secret=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&grant_type=refresh_token" https://oauth2.googleapis.com/token
Let’s Create a Google Sheet and Obtain the Spreadsheet ID
There is a unique identifier for the spreadsheet that can be found in the URL of the spreadsheet. For example, if the URL of the spreadsheet is: https://docs.google.com/spreadsheets/d/1w5TSDYt6Cbg8-_liMWDgTxgcSAb8JdpbPVTnzfj7E1I/edit
Then the spreadsheet ID is: 1w5TSDYt6Cbg8-_liMWDgTxgcSAb8JdpbPVTnzfj7E1I
We will use this ID to access the spreadsheet using the API.
Accessing Google Sheets API using Sell Script
Once we have obtained the access token and refresh token, we can start writing our shell script to access the Google Sheets API. There are various ways to interact with REST APIs using shell script, but we will use the cURL command. cURL command is a versatile tool that allows us to make HTTP requests from the command line.
Here is a shell script that inserts data into Google Spreadsheet:
#!/bin/bash
# Define Some variable values to insert
value1="Server01"
value2="/var/www"
value3="85% Used"
# Set the spreadsheet ID and range
SPREADSHEET_ID="1w5TSDYt6Cbg8-_liMWDgTxgcSAb8JdpbPVTnzfj7E1I"
RANGE="Sheet1!A1:C1"
# Set the client ID and client secret
CLIENT_ID="251327740099-6667am9lnhjeffc22cequ1gdo1uchcbb.apps.googleusercontent.com"
CLIENT_SECRET="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"
REFRESH_TOKEN="1//0gg3yvsZe2NNVCgYIARAAGBASNwF-L9IrLiIg4_bSFdSBjz9OBrewqMMDHJWd1NNjUc_Kkw_O7MfJUXPHAJySNmK44KHD0KYy9o0"
# Construct cURL command to get Access Token
AUTH_RESPONSE=$(curl --request POST --data "access_type=offline&refresh_token=$REFRESH_TOKEN&client_id=$CLIENT_ID&client_secret=$CLIENT_SECRET&grant_type=refresh_token" https://oauth2.googleapis.com/token)
ACCESS_TOKEN=$(echo $AUTH_RESPONSE | jq -r '.access_token')
# Send the request to Google Sheets API
curl -s -X POST \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Content-Type: application/json" \
-d "{\"values\": [[\"$value1\", \"$value2\", \"$value3\"]]}" \
"https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/$RANGE:append?insertDataOption=INSERT_ROWS&valueInputOption=USER_ENTERED"
Once you successfully executed the above script you can see the following JSON output on the terminal and data in the spreadsheet.
Conclusion
In conclusion, accessing Google Spreadsheets using shell script can be a powerful way to automate regular SysAdmin workloads. By using the Google Sheets API and OAuth 2.0 authentication, we can securely access and manipulate data in Google Sheets. The cURL command is a versatile tool that allows us to easily interact with REST APIs from the command line. By combining these tools, we can smoothly automate many tasks related to Google Sheets.
