Convert a Google Sheets range to JSON with TABLE_TO_JSON

Data GenerationCyrus Radfar
Jul 7

The TABLE_TO_JSON function is a useful transformation for preparing data to sent to developers or an API.

There are multiple great uses, including quickly taking data from a spreadsheet into a variable in code.

Function Signature

=TABLE_TO_JSON(table, key_location, no_encode_keys, keep_empty_keys, field_indexes)

Argument Definitions

input [range]

  • required - range of cells to transform with the keys (headers) included in the range.

key_location [number, default = 0]

The location of the key names, or headers.

  • 0 - first row
  • 1 - last row (bottom row)
  • 2 - first column
  • 3 - last column (far-right column)

no_key_encode [boolean, default = false]

  • Keys are underscore encoded and pushed to lower-case by default. Turn off this feature by setting no_key_encode to true.

no_value_encode [boolean ,default = false]

  • By default, URL encodes keys by replacing unsafe ASCII characters with a "%" followed by two hexadecimal digits. When TRUE, the values aren't transformed.

field_indexes [string]

  • Field indexes will create an index so it's easier to lookup a key directly.
  • By default, there are no indexes so finding all matches for a value of a key or a single match requires a user to iterate the entire array of objects.
  • By indexing, the output includes the index which maps the key to the exact objects (by index).
  • Note names must match exactly with the Key Name.

Use the Cheat

How to Add a Cheat to a Google Sheet

Click the Code Box to Copy

Cheat copied to clipboard ✂️👍
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

We've built an interactive tutorial in Google Sheets where it's easy to see how the function works. You can read the definition, edit examples, and do some practice examples to verify that you understand how to use the function.

Copy TutorialView TutorialDownload AppScript Code
Convert a range to a JSON object with multiple items.
Cyrus RadfarCyrus Radfar

Cyrus is the friendly full-stack founder of spread_cheats. He's a work optimization fanatic.

He's also working on other projects through V1.

Related Cheats