Google Sheets API
Table of Contents
-
- 4.1. For Python
- 4.2. For C#
Introduction
Prerequisites
- You have a Google Cloud Platform (GCP) account.
- You have created a spreadsheet in Google Drive.
Preparation for Using the API
1Enable the API
From the GCP menu, select APIs & Services > Library. Search for "Google Sheets API" in the search bar and enable it (Figure 1).
2Create a Service Account
From the GCP menu, go to IAM & Admin > Service Accounts. Click "Create Service Account" (Figure 2), enter an account name, and create it.
Figure 2. Create a Service Account
3Create a Key
In the "Keys" tab of the created service account, click ADD KEY > Create new key (Figure 3), select JSON as the key type, and download it (Figure 4). The JSON file will be used for API calls from your program. This JSON file contains credentials for accessing the API, so keep it secure.
4Add the Service Account as an Editor to the Spreadsheet
Next, add the service account created in step 2 as an editor to your Google Drive spreadsheet. Click the "Share" button in the upper right, and enter the service account's email address in the input field (Figure 5). When the screen changes (Figure 6), confirm that "Editor" is selected and click the "Send" button.
With these steps, you have completed the preparation for using the API. Next, let's try calling the API from a program.
Calling the API from a Program
For Python
Install the libraries for working with Google APIs.
pip install google-api-python-client google-auth-httplib2 google-auth-oauthlib
Place the downloaded JSON credentials file in the same directory as your py file. The filename is sheet_credentials.json.
import google.auth
import googleapiclient.discovery
CREDENTIALS_FILE = 'sheet_credentials.json'
SPREADSHEET_ID = '1DgV4pYzg19ZyRKEWlGuAFXt_CnsDNSsKnpG5doM9Rjk'
try:
# Load credentials
credentials, _ = google.auth.load_credentials_from_file(
CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets']
)
# Build the Google Sheets API service
service = googleapiclient.discovery.build('sheets', 'v4', credentials=credentials)
# Set the values to update
values = [['こんにちは、Python の世界!']]
body = {'values': values}
# Execute the API request
result = (
service.spreadsheets()
.values()
.update(
spreadsheetId=SPREADSHEET_ID,
range='シート1!A1',
valueInputOption='USER_ENTERED',
body=body,
)
.execute()
)
print(f'Number of updated cells: {result.get("updatedCells")}')
except Exception as e:
print(f'An error occurred: {e}')
SPREADSHEET_ID
You can find this in the browser's address bar when you open the spreadsheet. The string betweend/and/editin the URL is the spreadsheet ID.values
As stated in the official documentation 1, set a 2D array in row-major order.
values = [
[
# Cell values ...
],
# Additional rows ...
]
When you run the program, you can confirm that the values are set (Figure 7).
Figure 7. Spreadsheet
For C#
Install the Google.Apis.Sheets.v4 package into your project.
dotnet add package Google.Apis.Sheets.v4
or
Install-Package Google.Apis.Sheets.v4
Similar to the Python case, place the JSON credentials file in the same directory as your application's executable file. The filename is sheet_credentials.json.
using Google.Apis.Auth.OAuth2;
using Google.Apis.Services;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
// Spreadsheet ID
const string spreadsheetId = "1DgV4pYzg19ZyRKEWlGuAFXt_CnsDNSsKnpG5doM9Rjk";
try
{
// Load credentials
using var stream = new FileStream("sheet_credentials.json", FileMode.Open, FileAccess.Read);
var credential = GoogleCredential.FromStream(stream).CreateScoped(SheetsService.Scope.Spreadsheets);
// Create SheetsService
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
});
// Create values to update
var values = new[] { new[] { "こんにちは、C# の世界！" } };
var valueRange = new ValueRange { Values = values };
// Create update request
var request = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, "シート1!A1");
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
// Execute the request
var response = request.Execute();
Console.WriteLine($"Number of updated cells: {response.UpdatedCells}");
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
The execution result is shown below (Figure 8).
Troubleshooting
HttpStatusCode is Forbidden
If the following error message is displayed, it indicates that you do not have permission to edit the spreadsheet.
The service sheets has thrown an exception. HttpStatusCode is Forbidden. The caller does not have permission
Possible causes are:
- The service account has not been added as an editor to the spreadsheet.
- You are referencing a credentials file from a different service account.
Unable to parse range
The sheet name specified must match the actual sheet name in the spreadsheet.
The service sheets has thrown an exception. HttpStatusCode is BadRequest. Unable to parse range: sheet1!A1
Summary
We have explained the steps to update spreadsheets programmatically using the Google Sheets API. By using the API, you can automate manual workflows and expand the scope of applications, such as treating spreadsheets like a database.
For more details on the API, please refer to the references.
