ConvertCalculator Icon

GOOGLESHEET Function

This article explains how to use GOOGLESHEET functions

Sample Usage

FS
GOOGLESHEET("1qYJVonS_caYCc1diSOYb0HM-s5nUyxW6PsfsvzIoJK9I", "2050638375")

Syntax

FS
GOOGLESHEET(spreadsheet_id, sheet_id)
  • spreadsheet_id
    • The ID string of the Google spreadsheet, that can be found in the URL of the spreadsheet
  • sheet_id
    • The ID string of the sheet (or sometimes called worksheet), that can be found in the URL of the spreadsheet.

Notes

  • Before you can use the GOOGLESHEET function you need to connect your Google account. You can set connect your account in the connections section of the account page.
  • The return value from this function is a matrix (or table-like dataset) with rows and columns.
  • You can find the spreadsheet_id and sheet_id in the URL of your google spreadsheet. Open your spreadsheet and find the spreadsheet_id and sheet_id here:
Google Sheets webpage

Examples

You can use the data from the GOOGLESHEET function exactly like how you would use data from datasheets. Here are some examples:

Returning a single value from the matrix

If you want to return a single value, you can do so in conjunction with the INDEX function:

FS
INDEX(GOOGLESHEET("spreadsheet_id", "sheet_id"), 0, 0)
This returns the value in the cell of the first row of the first column. You can use this together with other inputs (like questions or variables) to make it a bit more interesting:
FS
INDEX(GOOGLESHEET("spreadsheet_id", "sheet_id"), QA, QB)

Now the value is returned based on the outcome of QA and QB.

Using VLOOKUP or HLOOKUP

Using VLOOKUP or HLOOKUP works exactly the same as with datasheets:

FS
VLOOKUP(123, GOOGLESHEET("spreadsheet_id", "sheet_id"), 1, 0)
This will find the row where 123 is the first column and returns the second column. Again, let's make it more interesting:
FS
VLOOKUP(QA{text}, GOOGLESHEET("spreadsheet_id", "sheet_id"), QB, 1)

Now we're talking! If a user fills out a text field, it searches all the rows of the first column of your spreadsheet to find a partial match. Depending on the value of QB it returns that column.

Using SUMIF, SUMIFS, FINDIF, FINDIFS

Using SUMIF, SUMIFS, and FINDIFS is possible as well. It works a little bit differently when using GOOGLESHEET() compared to datasheets:

Datasheet

FS
SUMIFS(DA_C, DA_A, QA, DA_B, QB)

GOOGLESHEET()

FS
VA = GOOGLESHEET("spreadsheet_id", "sheet_id")

FINDIFS(COLUMN(VA, 2), COLUMN(VA, 0), QA, COLUMN(VA, 1), QB)

In this example we first created a variable (VA) for getting the spreadsheet data. then for FINDIFS we need to reference the columns A, B, and C a little bit differently:

  • DA_C = COLUMN(VA, 2)
  • DA_A = COLUMN(VA, 0)
  • DA_B = COLUMN(VA, 1)

Note that counting starts at 0, so the first column (A) is referred to by 0, the second column by 1 etc.

This works the same for SUMIF and SUMIFS.

Troubleshoot issues

  • If your GoogleSheet contains merged cells, the formula will throw a #NAME-error. This error occurs when the app doesn't recognize something in your formula. In this case, the merged fields provide an unexpected input.
  • Some browsers like Opera have aggressive caching mechanisms that prevent your GoogleSheet data to not update in real-time. When you run into this problem, try switching to an alternative browser like Chrome or Safari.