Have you ever found yourself in a situation where you needed to convert data from your Google Sheets spreadsheet to JSON format? Perhaps you needed to use it as input for another app or system. Well, worry no more! In this article, we’ll show you how to effortlessly convert your data into the JSON format.
Simplify Your Data with JSON
JSON data can have complex nested structures, but a typical format you’ll encounter is either a simple array or a key-value (dictionary) format. For example:
Array Format:
["Alice", "Bob", "Eve", "Mary"]Key-Value Format:
{"Colombia": "Bogota", "Czechia": "Prague", "Philippines": "Manila", "Japan": "Tokyo"}Converting your Google Sheets data into JSON format is a breeze with a simple formula.
Convert a Range of Rows into a JSON Array
Let’s start with an example of converting a range of rows from one column into a simple JSON array. Take a look at the image below.
The formula used in the example above is as follows:
=ArrayFormula(concatenate("[", join(", ", char(34)&A1:A4&char(34)), "]"))This formula uses the ArrayFormula function to apply the formula to each row in the selected range (A1:A4). The formula encloses each row value with quotes (char(34)) and then joins them together. Finally, it adds the opening and closing square brackets to form a JSON array.
Convert Two Columns into a Key-Value JSON Format
Now let’s dive into a more complex example involving two columns. In this case, the first column represents the “key” and the second column represents the “value.” Take a look at the image below.
The formula used for this example is as follows:
=ArrayFormula(concatenate("{", join(", ", char(34)&A2:A5&char(34)&char(58)&char(32)&char(34)&B2:B5&char(34)), "}"))Although this formula may seem complex, it is quite similar to the previous one. We are using two separate ranges of cells and replacing the square brackets with curly braces. The char() function is used to represent different characters:
char(34)represents a double quote.char(58)represents a colon.char(32)represents a space.
In summary, the ArrayFormula() function is the key Google Sheets function to use here. By combining the data for each element using the join() function and then utilizing the concatenate() function, you can effortlessly create the opening and closing braces needed for JSON formatting.
Wrap Up
Converting your Google Sheets data to JSON format doesn’t have to be a daunting task. By following the simple formulas we’ve provided, you can easily transform your data into the desired JSON structure. We hope this little tutorial proves useful!
To learn more about Google Sheets and other useful tips and tricks, head over to Crawlan.com – your go-to resource for all things spreadsheets and more. Happy converting!
