Google Sheets

Explore 3 actions and their permissionsExact inputs, sign-in scopes, access limits and approval rules.

With Google Sheets, a hire can:

• see a spreadsheet's sheets (tabs) and their sizes,
• read cells, like Invoices!A1:F100 or a whole sheet, as shown, as raw numbers, or as formulas,
• add rows after the last row of a table, like a log, a sign-up list or a tracker, if you allow it.

A hire names a spreadsheet by its address or ID. To find spreadsheets by name or contents, connect Google Drive too.

1. Make an app in Google CloudCopy Anchor Link

If you already made one for another Google tool, use the same app: turn on the Sheets API and add the scope below.

1. Open console.cloud.google.com, and create a project (or pick one).
2. APIs & Services → Library: search for Google Sheets API and click Enable.
3. APIs & Services → OAuth consent screen: Internal with Google Workspace; with a personal account, External, add yourself as a test user, then Publish app (in testing, Google stops the access after 7 days).
4. Add the scope https://www.googleapis.com/auth/spreadsheets.readonly, or https://www.googleapis.com/auth/spreadsheets if hires should add rows.
5. Credentials → Create credentials → OAuth client ID, type Web application. Under Authorized redirect URIs, add the address Roster shows on the Google Sheets connection. It ends in /api/integrations/google-sheets/oauth/callback.
6. Copy the Client ID and Client secret.

2. Connect it in RosterCopy Anchor Link

1. Open Integrations → Tools → Add a tool, search for Google Sheets and choose it.
2. Paste the OAuth app client ID and OAuth app client secret.
3. What hires may do: Read spreadsheets, or Read spreadsheets and add rows. Roster asks Google only for what you choose here. If you change it later, press Connect again.
4. Click Connect, then Connect with Google Sheets on the new connection, sign in and click Allow.

3. Give hires accessCopy Anchor Link

On the hire's Tools tab, click Give access on Google Sheets:

• Read only: it can list sheets and read cells.
• Read and write: it can also add rows, when the connection allows it.
• Spreadsheets: IDs of the spreadsheets it may use: the part after /d/ in the address. Leave it empty for every spreadsheet the account can open.

Rows are added exactly as the hire writes them, so text that starts with = stays text. A hire can ask for values to be read as if a person typed them (dates, numbers, formulas) when that's what's wanted.

If something goes wrongCopy Anchor Link

• "Isn't connected yet": press Connect with Google Sheets on the connection.
• Adding rows is refused with 403: the connection was connected for reading only. Choose Read spreadsheets and add rows and press Connect again.
• "No spreadsheet": the connected account can't open it. Share it with that account.

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "properties": {
    "spreadsheet": {
      "type": "string",
      "minLength": 5,
      "maxLength": 300,
      "description": "the spreadsheet's ID or its address"
    }
  },
  "required": [
    "spreadsheet"
  ]
}

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "properties": {
    "spreadsheet": {
      "type": "string",
      "minLength": 5,
      "maxLength": 300,
      "description": "the spreadsheet's ID or its address"
    },
    "range": {
      "type": "string",
      "minLength": 1,
      "maxLength": 200,
      "description": "A1 notation, e.g. Invoices!A1:F100, or a sheet name"
    },
    "values": {
      "default": "formatted",
      "description": "as shown, raw numbers, or the formulas themselves",
      "type": "string",
      "enum": [
        "formatted",
        "unformatted",
        "formulas"
      ]
    }
  },
  "required": [
    "spreadsheet",
    "range"
  ]
}

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "properties": {
    "spreadsheet": {
      "type": "string",
      "minLength": 5,
      "maxLength": 300,
      "description": "the spreadsheet's ID or its address"
    },
    "range": {
      "type": "string",
      "minLength": 1,
      "maxLength": 200,
      "description": "the sheet's name or the table's range, e.g. Log!A:D"
    },
    "rows": {
      "minItems": 1,
      "maxItems": 200,
      "type": "array",
      "items": {
        "minItems": 1,
        "maxItems": 50,
        "type": "array",
        "items": {
          "anyOf": [
            {
              "type": "string",
              "maxLength": 5000
            },
            {
              "type": "number"
            },
            {
              "type": "boolean"
            }
          ]
        }
      }
    },
    "as_typed": {
      "default": false,
      "description": "read values as if a person typed them (dates, numbers, formulas starting with =)",
      "type": "boolean"
    }
  },
  "required": [
    "spreadsheet",
    "range",
    "rows"
  ]
}