Google Sheets

You can directly connect to your Google Drive to explore your existing spreadsheets, combine them with other data sources and build apps on top of them as a data foundation.

Connecting your Google Drive

To connect your Google Drive, follow the steps below:

  1. Go to Data Management and click on "New source" to create a new data source.

  2. Select Google Sheets from the list of available data sources.

  3. Click on "Sign in with Google" and confirm the access request to Google Drive. After clicking "Allow" you will be redirected back to the configuration page.

  4. We load the names of all Google Sheets that exist in your Google Drive. In the select menu, you can select all Google Sheet files that you want to give access to. Note: We enforce the access restriction in our backend. So any query done to GDrive from the frontend will be checked by the backend to make sure there is no breach.

  5. Save your Google Drive connection with "Connect".

Note: You can configure the data source for two different environments, Staging and Production. You can find more information about it here.

You need to have edit permissions to use a spreadsheet in Uify. The list of files presented to you for sheet selection already applies that filter. In case you do not find a spreadsheet that you see in your Google Drive, please make sure that you have at least the Editor-role for that sheet. Edit permissions are required because Uify inserts specially constructed formulas for certain operations (e.g. primary key lookups, record counts, etc.). A hidden sheet is added to your spreadsheet to perform such formula operations.

Query your Google Sheets

Once you have connected your Google Drive to Uify and whitelisted the Google Sheet files that you want to allow access to, you can write queries to interact with your data source.

You create queries in your app editor. For that you first need to create an app and then click on "Edit" to open up the editor. Once in the editor, you open the action panel. To launch the action panel you click on the middle icon in the header. To create your query, you click on "+" right of the search and then select "Google Sheets query".

First of all, you need to select your data source. That is the exact data source that you connected in data management in the first place. Next, you select the type of query you want to create, i.e. what do you want your Google Sheet to do. There are 6 different types of queries.

Query Types

List spredsheets

It returns the id and name of Google Sheets files in your Google Drive.

You don't need any additional inputs for this query.

This is an example return value:

[{
    id: "1x5Fl0VCL6niHW5qGHxnOpobwHRCGN5bnnl9rCAAJs50",
    name: "Orders"
},
{
    id: "1qdvv58m91TWjCjbaGG6qSL_FB6NwhoFEzRGdskHANYg",
    name: "SKUs"
}]

Fetch range(s)

For a given range, it returns the content in the range. It excludes all empty cells after the last cell that contains a value (vertically and horizontally).

There the query expects three inputs: spreadsheet ID, value range(s) and value formatting. You'll find more info here.

Let's look at an example for the following table, the range A1:F13 and formatted original values.

The return value would be the following:

[
    ["", "Product", "Price"],
    ["", "iPhone", 899],
    ["", "MacBook", 1599],
    ["", "", ""],
    ["", "", "", "", "Contact"],
    ["", "", "", "", "til@example.com"]    
]

Fetch table(s)

This query returns a table starting from a specific cell. The width of the table is determined by the header column, i.e. a contiguous sequence of cells with values starting from the "left-most header cell".

There the query expects three inputs: spreadsheet ID, the left-most header cell and value formatting. Additionally, you can provide the optional return column types. You'll find more info here.

Let's look at an example for the following table, the left-most header cell B2 and formatting as original values.

The return value would be the following:

{
    headers: ["Product", "Price"],
    records: [
        ["iPhone", 899],
        ["MacBook", 1599],
        ["", 2499]
    ]
}

Append to table

This is a create operation. It will add the content specified in the body to your spreadsheet.

The query expects four inputs: spreadsheet ID, the left-most header cell, value formatting and the primary key header. The return column types is again optional. You'll find more info here.

Besides these four inputs, it requires you to provide the record that should be added in the body. You can provide an object specifying the row content or an array of objects. If you don't specify the values for all headers or the object includes wrong headers, only matching headers will be filled out. Note: The primary key always needs to be provided!

A sample body could look like this:

[
    {
        "Product": "Airpods",
        "Price": 249
    }
]

Update table records

This is an update operation. It will update an existing record in your spreadsheet.

The query expects four inputs: spreadsheet ID, the left-most header cell, value formatting and the primary key header. You can specify if you want the updated record to be returned by the query after the update. If that is the case, you can also set the return column types. You'll find more info here.

Besides the inputs, you need to provide the new record content that should be written in the spreadsheet. You provide it in the body. Similar to the append operation, you provide an object or an array of objects. Only the cells with headers that are mentioned in the object and match the column headers will be updated. Note: The primary key always needs to be provided.

A sample body could look like this (with primary key "Product"):

[
    {
        "Product": "MacBook",
        "Price": 1999
    }
]

Delete table records

This is a delete operation. It will delete an existing record in your spreadsheet specified by the primary key.

The query expects three inputs: spreadsheet ID, the left-most header cell and the primary key header. You'll find more info here.

Besides these inputs, you need to provide the primary keys of the records you wish to delete. The input takes a single primary key or an array of primary keys.

A sample body for our example spreadsheet with primary key "Product" could be:

[
    "MacBook",
    "iPhone"
]

Inputs

For ech query you need to provide a series of inputs. Here is a list of all inputs with an explanation:

Spreadsheet ID

A unique identifier of the spreadsheet that you try to fetch from. You can either run the "List spreadsheet" query to get the spreadsheet id or you will find it in the url of the Google Sheet: https://docs.google.com/spreadsheets/d/1x5Fl0VCL6niHW5qGHxnOpobwHRCGN5bnnl9rCAAJs50/edit#gid=0

Value range

The range from which the content should be fetched. It expects the cell range as A1 notation. You can target a value range in a specific sheet by adding the sheet name to the front. E.g.: Sheet1!A1:B6

Left-most header cell

The left starting cell of the header of the table you want to identify in the sheet. It expects the cell in A1 notation. You can target a cell in a specific sheet by adding the sheet name to the front. E.g.: Sheet1!B2

Value formatting

  • Original value: Returns the original value of the cell. I.e. a numeric value will be returned as a number.

  • Formatted string: Returns the values as strings. I.e. a numeric value will be returned as a string.

  • Formula: Returns the original value of the cell and for calculated cells the formula as a string.

Primary key header

Specifies the name of the column header in the spreadsheet that uniquely identifies each row. The input field expects a string.

The primary key is needed for the append query to check if a record with the provided key already exists and for the update and delete queries to identify the records that should be mutated.

E.g. If you have a table with columns id, name and email, the id could be the unique identifier.

(optional) Return column types

For some native values Google returns some unusual values. A date, for example, is by default returned as a numeric value. This input field allows you to transform the values of certain columns to your desired type. It expects an object with the headers as keys and the types as values.

E.g.: {"createdAt": "datetime", "checked?": "boolean"}

The supported types are:

  • number

  • string

  • boolean

  • json

  • date

  • datetime

PreviousBigQueryNextMicrosoft SQL

Last updated