Function Overview

The EVALJS function allows you to evaluate JavaScript code within a given context directly in your Google Sheets. This function is particularly 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 JSON data

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

Output: Alice is 30 years old

Example 3: Fetching and using JSON data from a URL

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

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

Example 4: Complex calculations

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

Output: 15

In this example, you can see that the code uses the special $ variable. This is always availabe and allows you to refer to the current context. Here, it refers to the array [1, 2, 3, 4, 5].

Syntax

=EVALJS(context, code)

Parameters

  • context (required): 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, optional): 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 1 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 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]