Sample: Script to update spreadsheets

To demonstrate how Workiva Scripting works in Workiva, we've built a simple script that updates a cell in a spreadsheet. You can follow these steps to replicate the script in your own workspace.

Note: Scripting is currently only available through Customize Workiva. Learn more.

Requirements

Workiva Scripting must be enabled for your workspace. Learn more about Workiva Scripting.
The Script Editor role is required to create or edit scripts.
The API grant used by this example must have the file:write scope and access to the target spreadsheet. For instructions, see Workiva API Setup.
You must generate dependencies in order to run this script directly from a spreadsheet. This will include the "requests" library necessary to interact with the Workiva Public APIs.

Build the script

To build the script in your workspace, follow these steps:

From Home, click + Create => Script.
On the right side, enter a name and brief description for the script.

Go to the script editor in the middle of the screen and delete any existing code. Copy and paste in the following:

import os
import time

import requests


AUTH_URL = "https://api.app.wdesk.com/oauth2/token"
SS_API_URL = "https://api.app.wdesk.com/spreadsheets/"
API_VERSION = "2026-01-01"
REQUEST_TIMEOUT = 30
OPERATION_TIMEOUT = 120

CLIENT_ID = os.getenv("CLIENT_ID")
CLIENT_SECRET = os.getenv("CLIENT_SECRET")
SPREADSHEET_ID = os.getenv("SPREADSHEET_ID")
SHEET_ID = os.getenv("SHEET_ID")
HELLO_WORLD = os.getenv("HELLO_WORLD")


class ApiAuth:
    def __init__(self):
        self._headers = {
            "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
            "X-Version": API_VERSION,
        }

    def getAuthToken(self):
        tokenRes = requests.post(
            AUTH_URL,
            data={
                "client_id": CLIENT_ID,
                "client_secret": CLIENT_SECRET,
                "grant_type": "client_credentials",
            },
            headers=self._headers,
            timeout=REQUEST_TIMEOUT,
        )
        tokenRes.raise_for_status()
        return tokenRes.json()["access_token"]


class SSApi:
    def __init__(self, accessToken):
        self._accessToken = "Bearer " + accessToken
        self._ssApiUrl = (
            SS_API_URL
            + SPREADSHEET_ID
            + "/sheets/"
            + SHEET_ID
            + "/values/A2:H"
        )
        self._headers = {
            "Authorization": self._accessToken,
            "X-Version": API_VERSION,
        }

    def getRawData(self):
        dataRes = requests.get(
            self._ssApiUrl,
            headers=self._headers,
            timeout=REQUEST_TIMEOUT,
        )
        dataRes.raise_for_status()
        rawData = dataRes.json()
        return rawData["data"][0]["values"]

    def waitForOperation(self, operationUrl):
        deadline = time.monotonic() + OPERATION_TIMEOUT

        while time.monotonic() < deadline:
            operationRes = requests.get(
                operationUrl,
                headers=self._headers,
                timeout=REQUEST_TIMEOUT,
            )
            operationRes.raise_for_status()
            operation = operationRes.json()

            if operation["status"] == "completed":
                return
            if operation["status"] == "failed":
                raise RuntimeError(
                    operation.get("failureReason", "Spreadsheet update failed")
                )

            time.sleep(float(operationRes.headers.get("Retry-After", "2")))

        raise TimeoutError("Spreadsheet update did not complete before the timeout")

    def updateRange(self, sheetId, range, values):
        url = (
            SS_API_URL
            + SPREADSHEET_ID
            + "/sheets/"
            + sheetId
            + "/values/"
            + range
        )
        print("Update Range:", url)
        print("Values:", values)

        dataRes = requests.put(
            url,
            headers=self._headers,
            json=values,
            timeout=REQUEST_TIMEOUT,
        )
        dataRes.raise_for_status()

        operationUrl = dataRes.headers.get("Location")
        if not operationUrl:
            operationUrl = dataRes.json().get("operationLocation")
        if not operationUrl:
            raise RuntimeError("Spreadsheet update did not return an operation URL")

        self.waitForOperation(operationUrl)


def write_tier_data(ssApi):
    ssApi.updateRange(SHEET_ID, "A1", {"values": [[HELLO_WORLD]]})


def main():
    authToken = ApiAuth().getAuthToken()
    ssApi = SSApi(authToken)
    write_tier_data(ssApi)


print("Calling main")
main()

Click Save.

At this point, you should ensure you've generated the necessary dependencies for this script.

Run the script

You must generate dependencies before running this script. The dependency bundle includes the requests library used to call the Workiva Public APIs.

With the script open, click Run Script in the menu bar at the top of the screen.
Enter the parameters needed to access the Spreadsheets API, the spreadsheet and sheet IDs, and the text content for the cell. Click Run Script.

Note: The IDs can be found in the spreadsheet's URL: `/spreadsheet/{spreadsheetId}/sheet/{sheetId}`.

To verify the script completes, check the updated cell in the spreadsheet.

Run the script directly from a spreadsheet

Workiva automations provide the spreadsheet and sheet IDs through environment variables but do not pass custom runtime inputs. To use this script with an automation, replace the runtime-input declarations in the original script. Restrict access to the script to authorized users because users with source permissions can view the values defined in its code.

Replace:

CLIENT_ID = os.getenv("CLIENT_ID")
CLIENT_SECRET = os.getenv("CLIENT_SECRET")
SPREADSHEET_ID = os.getenv("SPREADSHEET_ID")
SHEET_ID = os.getenv("SHEET_ID")
HELLO_WORLD = os.getenv("HELLO_WORLD")

With:

CLIENT_ID = "your_client_id"
CLIENT_SECRET = "your_client_secret"
SPREADSHEET_ID = os.getenv("INPUT_SPREADSHEET_ID")
SHEET_ID = os.getenv("INPUT_SPREADSHEET_SHEET_ID")
HELLO_WORLD = "Hello World"

Replace the credential placeholders and cell value with your values.
Save the script.
In the spreadsheet’s Automations panel, create an automation with a Manual Execution trigger.
Add an Execute script action and select this script.
Save and run the automation.

To learn more, see Execute scripts with automations .

Workiva Support Center