Install this add-on from the Google Workspace Marketplace to get access to the EVALJS custom function. It allows you to run sandboxed JavaScript code in your Google Sheets™. Useful for processing JSON data, working with external APIs, or performing complex calculations using JavaScript.

Examples

Example 1: Simple calculation:

=EVALJS("5+2")

Output: 7

Example 2: Working with context:

=EVALJS("hello", "$[0] + ' world'")

Output: hello world

Note that "hello" is provided in the first argument. All arguments except the last (which stands for the executable code) are accessible via the zero-indexed $ array. The code in the second argument uses the first element of this array ($[0]) to access the first argument ("hello").

Example 3: Importing JSON into Sheets

=EVALJS("{""name"":""Alice"", ""age"":30}", "Object.entries($[0])")

The output is a table:

A B
1 name Alice
2 age 30

Example 4: Exporting from Sheets to JSON

Let’s say you have the following data in your A1:B2 cell range:

A B
1 name Alice
2 age 30

You can simply convert this into a JSON object by writing:

=EVALJS(A1:B2, "Object.fromEntries($[0])")

Output: {"name":"Alice", "age":30}

Example 5: Transforming JSON data

=EVALJS("{""name"": ""Alice"", ""age"": 30}", "name + ' is ' + age + ' years old'")

Output: Alice is 30 years old

Here we use the name and age properties of the context provided in the first argument. All top-level properties of the context are accessible this way.

(The special $ variable referring to the context is available too. Using this, we could also write $.name and $.age to refer to name and age.)

Example 6: Fetching and using JSON data from a URL

=EVALJS("https://randomuser.me/api/", "results[0].name.first + ' ' + results[0].name.last")

Output: John Doe (example)

This will fetch data from the Random User API and return the first and last name of the generated user.

Example 7: Complex calculations

=EVALJS("[1, 2, 3, 4, 5]", "$[0].reduce((sum, num) => sum + num, 0)")

Output: 15

Syntax

=EVALJS(code)

The JavaScript code will be executed as-is (see Example 1) above and its result will be written in the current cell.

=EVALJS(url)

A GET HTTP/S request will be made to the provided URL and its result will be written in the current cell.

=EVALJS(context, code)

Parameters

  • context: The context in which to evaluate the code. This can be:
    • A valid URL from which to fetch JSON data
    • A JSON string
    • A reference to a cell (e.g. A1) or to a range of cells (e.g. A1:C10)
    • Empty string ("") In case you want to run JavaScript code without any context
  • code (string): The JavaScript code to evaluate within the context. Use the special $ variable to refer to the current context. ES6 syntax is supported.

Return Value

The function returns the result of the evaluation, which can be:

  • A single value. This will populate the same cell the EVALJS expression is written into. The value can be either a JavaScript primitive type (number, string, date) or a JSON object which will be stored as a string in the cell.
  • An array of values. In this case the array will be broadcast across multiple cells (and columns, if the returned array is 2 dimensional). For example try: =EVALJS("", "[[1, 2], [3, 4]]"). This will fill 4 cells like this:
    A B
    1 2
    3 4

Tips and Best Practices

  • Ensure that your context and code parameters are free of syntax errors. A common pitfall is to escape double-quotes incorrectly. See Example 3 above for a correct usage.
  • When working with external APIs, be mindful of rate limits and data usage.
  • Use appropriate error handling in your code to manage potential exceptions or errors thrown.
  • For complex operations, consider breaking down your code into smaller, manageable parts, possibly across multiple =EVALJS(...) calls in different cells. This should make your code more readable and also help debugging.

Troubleshooting

If you encounter issues while using the EVALJS function, try the following:

  • Verify that your context is valid JSON or a proper URL.
  • Ensure your JavaScript code is syntactically correct.
  • If working with large datasets, try processing smaller chunks of data at a time.
  • If the result is not what you expect, use JSON.stringify(...) to better inspect what is being returned.
  • If an error occurs during the execution of your code or while fetching data from a URL, the function will return an error message as a string. Hovering the mouse cursor over the cell often gives more information about the error.

Contact Support

If you encounter any issues or have questions about using the evalJS Google Sheets™ addon, please don’t hesitate to send an email to:

[email protected]