Use Spreadsheet Formulas in API Bridge query

Formulas

Formulas are generally expressions used to make calculations easier in Google Sheets. Here, we can use formulas to query a Data Source with fixed values. You can make a query by referencing a single cell or a group of cells. These formulas can be specified in the Suffix URLs and Body section.

While making a query, formulas are surrounded by three plus signs "+++" to indicate that they are not a part of the URL.

A formula would look something like this

• +++Sheet2!A2+++ – This represents the cell A2 in the Sheet with the name "Sheet2".
• +++Sheet2!A3:A6+++ – This represents the cells ranged from A3 to A6 in the Sheet with the name "Sheet2".

Single cell referencing

To make a query referenced with a single cell value, provide the cell reference (For example: SheetName!A2) in the Suffix URL by enclosing the formula with three plus signs "+++" on both sides.

For example:

Info

Do note to always include sheet name in your formulas. Eg. +++SheetName!A2+++ is correct

If you want to make a request with referencing a cell in the Suffix URL (Lets say A3 in the above table) then it would be,

• /users/+++Sheet1!A3+++ – This would generate the URL like "/users/16".
• /users?id=+++Sheet1!A3+++ – This would be "/users?id=16".

Cell requests can also be made in the POST and PUT methods in the request body.

Multiple cell referencing

Multiple cell referencing is similar to single cell references. Except that the range is specified with a colon ” : “.

For example,

Lets say, we want to query by referencing the cells from A3 to A6. The formula for this would be "+++Sheet1!A3:A6+++".

Making multiple cell referencing in Body,

You can also make queries by referencing from multiple columns like,

• /users?gender=+++Sheet1!D2:D5+++&status=+++Sheet1E2:E5+++ – This makes three request (one request per row) each having gender and status from both columns

Warning

API Bridge supports referencing formulas more than once in the same query. In such cases make sure that all the formulas reference the number of in both Suffix URL and Post Body.