google_sheets_api_write_formula_to_cell_quickstart.py

1import os.path
2
3from google.auth.transport.requests import Request
4from google.oauth2.credentials import Credentials
5from google_auth_oauthlib.flow import InstalledAppFlow
6from googleapiclient.discovery import build
7from googleapiclient.errors import HttpError
8
9# If modifying these scopes, delete the file token.json.
10SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
11
12# The ID and range of a sample spreadsheet.
13SAMPLE_SPREADSHEET_ID = "your_spreadsheet_id_here"
14SAMPLE_RANGE_NAME = "Sheet1!A1"
15
16def main():
17    """Shows basic usage of the Sheets API.
18    Writes a formula to a cell.
19    """
20    creds = None
21    # The file token.json stores the user's access and refresh tokens, and is
22    # created automatically when the authorization flow completes for the first
23    # time.
24    if os.path.exists("token.json"):
25        creds = Credentials.from_authorized_user_file("token.json", SCOPES)
26    # If there are no (valid) credentials available, let the user log in.
27    if not creds or not creds.valid:
28        if creds and creds.expired and creds.refresh_token:
29            creds.refresh(Request())
30        else:
31            flow = InstalledAppFlow.from_client_secrets_file(
32                "credentials.json", SCOPES
33            )
34            creds = flow.run_local_server(port=0)
35        # Save the credentials for the next run
36        with open("token.json", "w") as token:
37            token.write(creds.to_json())
38
39    try:
40        service = build("sheets", "v4", credentials=creds)
41
42        # The formula to be written
43        values = [
44            ["=SUM(B1:B10)"]
45        ]
46        body = {
47            "values": values
48        }
49        
50        # Call the Sheets API to update the cell with the formula
51        result = service.spreadsheets().values().update(
52            spreadsheetId=SAMPLE_SPREADSHEET_ID, 
53            range=SAMPLE_RANGE_NAME,
54            valueInputOption="USER_ENTERED", 
55            body=body
56        ).execute()
57        
58        print(f"{result.get('updatedCells')} cells updated.")
59
60    except HttpError as err:
61        print(err)
62
63if __name__ == "__main__":
64    main()