Google Sheets

Overview

Google Sheets is a spreadsheet web app developed by Google. You and your teammates can use Google Sheets to create and edit tabular or structured data collaboratively online.

Google Sheets provider allow you to use Google Sheets as backend, which the actual data can be managed by your operation team in online spreadsheet collaboratively. Through Google Sheets provider, you can query data from your spreadsheet using the Query Language and update data with the help of low level functions in io.framely.provider.GoogleSheetsConnection.

manage-data-on-two-sides Operations on Framely and Google Sheets

Note

Before you start, make sure you have created a spreadsheet.

Connection

To begin with, you need to build a connection between Google Sheets and the provider so that the provider gets access to your spreadsheet. To build the connection, get your spreadsheet Id and a service account credential then fill out the following form.

connection

Once you create a service account, you need to give this account permission to view or edit your spreadsheet in three steps:

  1. Copy the email of your service account.
  2. Go to your spreadsheet. At the top-right, click Share.
  3. Paste the email you copied and give the right permission to this service account. For example, if you don't need to update business data in your spreadsheet, set the service account as a Viewer, otherwise, set it as an Editor.

permit

Function Implementation

As mentioned in Implement Functions, there are two kinds of ways to implement a function:

  • In provider-dependent functions, use the Query Language to implement.

    • ❗Provider-dependent functions should always return a multi-value frame(even if the function returns only one row), in which the names of slots are the same as the names of columns, and the slot's type is compatible with the return column's type in the same index.
    • For example, if the slots in a frame are [id, name] of which types are [kotlin.Int, kotlin.String], the slots in return columns should be [id, name] as well, and the types of return columns are supposed to be [number, string] instead of [string, number].
  • In Kotlin functions, write function bodies in Kotlin.

    • Kotlin functions can be used to preprocess your data and then you call provider-dependent functions passing the processed data.

    • For example, in Google Sheets, you can't use is not null to compare null with a constant (both null is not null and 'xxx' is not null are illegal). Instead, you can first use Kotlin functions to convert a null value to a string(e.g. "null") and compare the value with "null" in provider-dependent functions.

      • In a provider-dependent function: getDate
      select A where inputParam != "null" and B = inputParam
      
      • In a Kotlin function
      // 1. Convert inputParam to "null" if it's null
      if(inputParam == null){
          inputParam = "null"
      }
      // 2. Call the provider-dependent function
      getDate(inputParam)
      
    • Learn how to implement more Kotlin functions, check out Kotlin Function.

Types Conversion

  • When you call the provider-dependent function, use the function called toQueryString to convert parameters to the right format.
  • When the provider-dependent function returns a set of values in the Google Sheets data type, we put those values in the return frame you defined, so you can display or use these values in the Framely environment.

conversion Type Conversion Between Framely and Google Sheets

  • Here is the conversion between entities and Google Sheets data types:
EntityGoogle Sheets Data Type
kotlin.Int / kotlin.Floatnumber
Customized entity (Builder-created Entity) / kotlin.Stringstring
kotlin.Booleanboolean
java.time.LocalDate / java.time.YearMonthdate
java.time.LocalTimetimeofday
java.time.LocalDateTimedatetime

How To Write a Query

To get your business data from a spreadsheet, you can write a query in provider-dependent functions. A provider-dependent function implementation consists of Function Meta and Query.

provider-dependent-function

  • Function Meta is used to define optional parameters that are needed in your query. The key means the parameter's name and the value is the parameter's value.

    • The keys you can choose are range, headers, gid and sheet. To learn what each of the parameters means, check out Creating a Chart from a Separate Spreadsheet.
    • For example, because there is no from clause in Google Sheets, if you want to select data from a sheet that is not the first sheet, you need to specify which sheet to select from. You can use gid to link to the sheet's ID, or you can use sheet to link to the sheet's name.
  • Query is where you write a query using Query Language. Wrapping Kotlin expressions in ${}, you can reference input parameters in the provider-dependent function or return values from other functions.

    • When you reference values using Kotlin expressions,❗make sure you use connection.toQueryString(X) to convert value X to the right format.
    • For example, suppose you want to get a user's name by their ID. The user's name is stored in column B while ID in column A. If the input parameter is userId, you may write a query like this:
    select B where A = ${connection.toQueryString(userId!!)}
    

How To Update/Append Data

To update and append your business, Framely provides external functions: update and append. You can call these functions using connection.update and connection.append in Kotlin functions. Check out the definitions of these functions in io.framely.provider.GoogleSheetsConnection. To learn the source of the function, see spreadsheets.values.update and spreadsheets.values.append.

  • The input parameters are the same in these two functions.
NameTypeReference
rangekotlin.StringUse range to select specific ranges. To learn how to define it, see A1 notation.
valueskotlin.Any[]Use values to add/append a row of values by putting values in a list, likelistOf(a, b, c).
valueInputOptionkotlin.StringUse valueInputOption to determine how input data should be interpreted. To know what options you should choose, see ValueInputOption.
  • The return types are UpdateValuesResponse and AppendValuesResponse. You can use the methods in these classes to check if the response meets expectations. For example, if you expect to update 3 values, use UpdateValuesResponse.getUpdatedCells == 3 to check.

Suppose you want to update a user's delivery address and phone number in the range of "'UserInfo'!B5:C5". If the input parameters are address and phoneNumber, the Kotlin function will be like this:

var values = listOf(address, phoneNumber)
var result = connection!!.update("'UserInfo'!B2", values, "RAW")
// if the update is successful, return true, otherwise, return false
return result!!.getUpdatedCells() == 2
Last Updated: