This article explains how to use GOOGLESHEET functions
FSGOOGLESHEET("1qYJVonS_caYCc1diSOYb0HM-s5nUyxW6PsfsvzIoJK9I", "2050638375")
FSGOOGLESHEET(spreadsheet_id, sheet_id)
spreadsheet_id
string
of the Google spreadsheet, that can be found in the URL of the spreadsheetsheet_id
string
of the sheet (or sometimes called worksheet), that can be found in the URL of the spreadsheet.spreadsheet_id
and sheet_id
in the URL of your google spreadsheet. Open your spreadsheet and find the spreadsheet_id
and sheet_id
here:You can use the data from the GOOGLESHEET function exactly like how you would use data from datasheets. Here are some examples:
If you want to return a single value, you can do so in conjunction with the INDEX
function:
FSINDEX(GOOGLESHEET("spreadsheet_id", "sheet_id"), 0, 0)
FSINDEX(GOOGLESHEET("spreadsheet_id", "sheet_id"), QA, QB)
FSVLOOKUP(123, GOOGLESHEET("spreadsheet_id", "sheet_id"), 1, 0)
FSVLOOKUP(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, and FINDIFS is possible as well. It works a little bit differently when using GOOGLESHEET() compared to datasheets:
FSSUMIFS(DA_C, DA_A, QA, DA_B, QB)
FSVA = 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:
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
.
#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.