From Google Sheets API to Rows API

Don’t worry, we’ve got you covered. The Rows API is quite similar to the Google Sheets one. You can keep using your existing flows and easily adapt them to Rows.

Before You Start

You need to learn how to find some IDs to perform the HTTP requests. Most of this required information is available in the spreadsheet URL.

Examples

Reference

The table below lists the API methods to allow you to pull, update, or replace data in Rows or Google Sheets. Those endpoints are also covered throughout this document.

Authentication

One of the main differences between Google Sheets API and Rows API is the authentication process. Rows allows you to generate an API key to use along with the HTTP requests, while Google Sheets API follows the OAuth 2.0 process. Rows follows a simpler approach as you just need to go to your workspace settings and create your API key. (Click here to learn how to create an API key.)

As long as you get a valid API key, the process is similar to what you are used to doing with Google Sheets. You just need to add the following header to your HTTP requests:

{"Authorization": "Bearer <your_api_key>"}

Find spreadsheet information

In order to read or write spreadsheet values you need to find first the sheet ID in Google Sheets. That’s the same for Rows, but here you need to look for the table ID instead.

1. Get the spreadsheet ID.
2. In Google Sheets you need to use the spreadsheets.get method to list all of your sheets. Similarly, in Rows you can use this method to list all the existing pages and tables of a spreadsheet.

Example

The following HTTP requests get information about a given spreadsheet.

• Google Sheets: GET https://sheets.googleapis.com/v4/spreadsheets/1fyRelb-Faa_dwl9oUSUWANVjt_nT8putzf3CboPCnQA

  1  (...)
  2  "sheets": [
  3          {
  4              "properties": {
  5                  "sheetId": 0,
  6                  "title": "Employees",
  7                  "index": 0,
  8                  "sheetType": "GRID",
  9                  "gridProperties": {
  10                      "rowCount": 1000,
  11                      "columnCount": 26
  12                  }
  13              }
  14          },
  15          {
  16              "properties": {
  17                  "sheetId": 2039411956,
  18                  "title": "Employee Costs",
  19                  "index": 1,
  20                  "sheetType": "GRID",
  21                  "gridProperties": {
  22                      "rowCount": 1000,
  23                      "columnCount": 26
  24                  }
  25              }
  26          }
  27  (...)

• Rows: GET https://api.rows.com/v1beta1/spreadsheets/1zUjr8u76wVMFBlxvG1Ocx

  1  (...)
  2  "pages": [
  3          {
  4              "id": "23a33a33-48a0-4dca-8409-b9a1a47963f4",
  5              "name": "Employees Dashboard",
  6              "slug": "employees-dashboard",
  7              "created_at": "2023-01-31T11:10:04.352Z",
  8              "tables": [
  9                  {
  10                      "id": "98fc66bf-e8c9-4aff-881d-7d768902b6bf",
  11                      "name": "Employees",
  12                      "slug": "employees",
  13                      "created_at": "2023-01-31T11:10:04.358Z"
  14                  },
  15                  {
  16                      "id": "533a0e03-9378-4824-ba06-3ca550bd6cee",
  17                      "name": "Employee Costs",
  18                      "slug": "employee-costs",
  19                      "created_at": "2023-01-31T11:10:21.575Z"
  20                  }
  21              ]
  22          }
  23  (...)

Reading values

The Google Sheets API provides the spreadsheets.values collection to enable the simple reading and writing of values. In this case, we are going to explore the reading methods. The available operations are:

• Get.
  • Returns the values of an existing Sheet. For that, you need to call the following URL:
    • GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheet ID}/values/{range}
• BatchGet.
  • Returns one or more ranges of values from a spreadsheet. The caller must specify the spreadsheet ID and one or more ranges. For each range requested in the batch, the API returns the cells in the format above (array of arrays).

Both are very similar. The Get is used to return an array of arrays of cell values. The BatchGet is used to execute the same operation as the Get, but for multiple ranges at the same time.

In Rows, we don’t support the BatchGet method yet, but you can pull data from a single table with this API method. Get the spreadsheet ID and table ID from the previous request and build the following HTTP request:

• GET https://api.rows.com/v1beta1/spreadsheets/{spreadsheet ID}/tables/{table ID}/values/{range}

Example

Let’s assume you want to get the values from the Employees table in Rows or sheet in Google Sheets. For this example, assume the following values are available in those spreadsheets.

In order to get the values from those tables you need to use the next HTTP requests:

• Google Sheets: GET https://sheets.googleapis.com/v4/spreadsheets/1fyRelb-Faa_dwl9oUSUWANVjt_nT8putzf3CboPCnQA/values/'Employees'!A1:D

  • This HTTP request brings the following JSON response:

    1  {
    2      "range": "Employees!A1:D993",
    3      "majorDimension": "ROWS",
    4      "values": [
    5          [
    6              "name",
    7              "salary",
    8              "department",
    9              "office location"
    10          ],
    11          [
    12              "John Smith",
    13              "$50,000.00",
    14              "Marketing",
    15              "New York"
    16          ],
    17          [
    18              "Jane Doe",
    19              "$60,000.00",
    20              "Sales",
    21              "Chicago"
    22          ],
    23          [
    24              "Bob Jones",
    25              "$45,000.00",
    26              "Accounting",
    27              "Los Angeles"
    28          ]
    29      ]
    30  }

• Rows: GET https://api.rows.com/v1beta1/spreadsheets/1zUjr8u76wVMFBlxvG1Ocx/tables/98fc66bf-e8c9-4aff-881d-7d768902b6bf/values/A:D

  • This HTTP request brings the following JSON response:

    1  {
    2      "items": [
    3          [
    4              "name",
    5              "salary",
    6              "department",
    7              "office location"
    8          ],
    9          [
    10              "John Smith",
    11              "$50,000",
    12              "Marketing",
    13              "New York"
    14          ],
    15          [
    16              "Jane Doe",
    17              "$60,000",
    18              "Sales",
    19              "Chicago"
    20          ],
    21          [
    22              "Bob Jones",
    23              "$45,000",
    24              "Accounting",
    25              "Los Angeles"
    26          ]
    27      ]
    28  }

Writing values

In the same spreadsheets.values collection from Google Sheets you can notice there are two methods to write values in sheets:

• Append
  • Finds a table within the range provided by you and appends the values to the next available row of that table.
    • POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append
• Update
  • Replaces the values in the range of the sheet that you provide. It starts from the first row onwards and the values already available in the spreadsheet are not respected.
    • PUT https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}

Both use cases are valid. Usually you might need to use the Append method to create a table log. On the other hand, if you want to always have refreshed data you will end up using the Update method.

The same is possible with Rows. We also allow you to append values with this method. Alternatively, if you are feeling like replacing the existing data, then the overwrite method gets you covered.

Example

Let’s assume you want to append some values in the Employees table in Rows or sheet in Google Sheets. Consider the spreadsheet contains the same values as before.

The values you want to append on each spreadsheet are in the JSON array below.

1{
2  "values": [
3    [
4      "Richard Green",
5      "$50000.00",
6      "Sales",
7      "New York"
8    ]
9  ]
10}

Therefore, in order to send these values you need to call the HTTP methods to append those values on each spreadsheet.

• Google Sheets: POST https://sheets.googleapis.com/v4/spreadsheets/1fyRelb-Faa_dwl9oUSUWANVjt_nT8putzf3CboPCnQA/values/'Employees'!A1:D:append?valueInputOption=USER_ENTERED

  • You should send the values in the body of the request

    1  {
    2    "values": [
    3      [
    4        "Richard Green",
    5        "$50000.00",
    6        "Sales",
    7        "New York"
    8      ]
    9    ]
    10  }

• Rows: POST https://api.rows.com/v1beta1/spreadsheets/1zUjr8u76wVMFBlxvG1Ocx/tables/98fc66bf-e8c9-4aff-881d-7d768902b6bf/values/A:D:append

  • You should send the values in the body of the request

    1  {
    2    "values": [
    3      [
    4        "Richard Green",
    5        "$50000.00",
    6        "Sales",
    7        "New York"
    8      ]
    9    ]
    10  }

  • The API returns a 202 HTTP status code.

    In both cases you will get a new row with the information of Richard Green.

    

Let’s say now that you need to update the salary and office location of Jane Doe. What do you need to do? It’s quite similar in both cases.

• Google Sheets: PUT https://sheets.googleapis.com/v4/spreadsheets/1fyRelb-Faa_dwl9oUSUWANVjt_nT8putzf3CboPCnQA/values/'Employees'!A3:D3?valueInputOption=USER_ENTERED

  • As the body of the request you should send:

    1  {
    2    "values": [
    3      [
    4        "Jane Doe",
    5        "$100000.00",
    6        "Sales",
    7        "New York"
    8      ]
    9    ]
    10  }

• Rows: POST https://api.rows.com/v1beta1/spreadsheets/1zUjr8u76wVMFBlxvG1Ocx/tables/98fc66bf-e8c9-4aff-881d-7d768902b6bf/cells/A3:D3

  • As the body of the request you should send:

    1  {
    2    "cells": [
    3      [
    4        {
    5          "value": "Jane Doe"
    6        },
    7        {
    8          "value": "$100000.00"
    9        },
    10        {
    11          "value": "Sales"
    12        },
    13        {
    14          "value": "New York"
    15        }
    16      ]
    17    ]
    18  }

In both cases, you will end up with the new salary and office location of Jane Doe in the table.

Useful links

• Rows API technical documentation.
• Learn How to Manage your API Keys.
• Learn how to automate tasks using Zapier via Rows API

If you have any questions or feedback about Rows API beta, please contact our support team at support@rows.com