Update Spreadsheet
I can use the batchUpdate method to alter or update some appearance and operation of the spreadsheets. I can change the following data:
- Cell format and borders
- named rage
- protected range
- conditional formatting
These operations can be divided into three groups.
- Add (and duplicate): add new objects or duplicate existing one
- Update ( or set): update some properties and leaving others as they were.
- Delete: Delete objects
How it works is that batchUpdate will take one or more request objects, each one will modify, add or delete some parameter or value. I will take the information from the official documentation
Updating Spreadsheets | Sheets API | Google Developers
This table will be a short version of the information in the above link:
The official documentation also mentions request that mimics user actions, but the most relevant for me are:
Filed mask
These are comma-separated values that will ensure just the desire fields are changed. These "field masks" are required in some update requests. I can use '*' as a shorthand to update every field.
an example of these files in a JSON representation is:
| {
"requests": [{
"updateSpreadsheetProperties": {
"properties": {"title": "My New Title"},
"fields": "title"
}
}]
}
|
Some update requests will have a response. These returns are delivered in array form, where each response will occupy the same index that corresponds to the request.
Example
First, I will display the example from the documentation and later a customized example with few modifications.
| requests = []
# Change the spreadsheet's title.
requests.append({
'updateSpreadsheetProperties': {
'properties': {
'title': title
},
'fields': 'title'
}
})
# Find and replace text
requests.append({
'findReplace': {
'find': find,
'replacement': replacement,
'allSheets': True
}
})
# Add additional requests (operations) ...
body = {
'requests': requests
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=body).execute()
find_replace_response = response.get('replies')[1].get('findReplace')
print('{0} replacements made.'.format(
find_replace_response.get('occurrencesChanged')))
|
from the documentation code above:
- Create an empty array called
requests.
- Append the first update request
updateSpreadsheetProperties which will change the name of the spreadsheet.
- Append the second request
findReplace which finds a specific value and replaces it.
- I create the
body with the requests
- Execute the
batchUpdate request using the body I already create.
- Save and display the response from the
findReplacerequest.
For the example, I will start with the following spreadsheet
and I have the following method:
| def update_spreadsheet(gservice: gsheet_resource):
requests = []
requests.append({
'updateSpreadsheetProperties':{
'properties': {
'title': 'New_title'
},
'fields': 'title'
}
})
requests.append({
'findReplace':{
'find': "first",
'replacement': "second",
'allSheets': True
}
})
body = {
'requests': requests
}
response = gservice.spreadsheets().batchUpdate(
spreadsheetId=SAMPLE_SPREADSHEET_ID,
body=body
).execute()
find_replace_response = response
print(f'{find_replace_response}')
|
The code about it is similar to the example from the documentation with some minor changes:
- I add the changes in the title of the spreadsheet and define the strings I want to find and replace
- I print the full response for this request
The response of this request is:
| {'spreadsheetId': '1DjfIxE0ValkVRVyIOVE-8Sa9v7xSkqDvY9NCWGhImGp', 'replies': [{}, {'findReplace': {'valuesChanged': 2, 'rowsChanged': 2, 'sheetsChanged': 1, 'occurrencesChanged': 2}}]}
|
I have the ID of the spreadsheet, and the array that represents the response to the different requests
| 'replies': [{}, {'findReplace': {'valuesChanged': 2, 'rowsChanged': 2, 'sheetsChanged': 1, 'occurrencesChanged': 2}}]}
|
The first item in the array is empty because the first request was the change of the name. The second is the request to find and replace a string. The response looks like {'findReplace': {'valuesChanged': 2, 'rowsChanged': 2, 'sheetsChanged': 1, 'occurrencesChanged': 2}}]} we find 2 occurrences of the string first and replace it with the string second affecting two rows in one sheet.
now the spreadsheet looks like this:
The script
| from __future__ import print_function
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
# Creating an alias for google Resource class
gsheet_resource = 'googleapiclient.discovery.Resource'
# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '1DjfIxE0ValkVRVyIOVE-8Sa9v7xSkqDvY9NCWGhImGp'
SAMPLE_RANGE_NAME = 'first_sheet!A1:C4'
def fetch_resource() -> gsheet_resource:
"""Shows basic usage of the Sheets API.
Prints values from a sample spreadsheet.
"""
creds = None
# The file token.json stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', SCOPES)
# If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
'credentials.json', SCOPES)
creds = flow.run_local_server(port=0)
# Save the credentials for the next run
with open('token.json', 'w') as token:
token.write(creds.to_json())
return build('sheets', 'v4', credentials=creds)
def update_spreadsheet(gservice: gsheet_resource):
requests = []
requests.append({
'updateSpreadsheetProperties':{
'properties': {
'title': 'New_title'
},
'fields': 'title'
}
})
requests.append({
'findReplace':{
'find': "first",
'replacement': "second",
'allSheets': True
}
})
body = {
'requests': requests
}
response = gservice.spreadsheets().batchUpdate(
spreadsheetId=SAMPLE_SPREADSHEET_ID,
body=body
).execute()
find_replace_response = response
print(f'{find_replace_response}')
if __name__ == '__main__':
service = fetch_resource()
update_spreadsheet(service)
|