Skip to main content
3 votes

Parse JSON from Google Spreadsheet Cell

You can build a custom parser for any fixed JSON string using regular expressions. For example, if you want to extract the child items into columns you can do the following (assuming your JSON string ...
Cornelius Roemer's user avatar
2 votes
Accepted

Regex for JSON parsing in Google Spreadsheet Cell

=TRANSPOSE( REGEXEXTRACT( A1, "\" & REGEXREPLACE( A1, CHAR(34)&"count" & CHAR(34) & ":(\d+)", CHAR(34)&&...
David Tan's user avatar
  • 1,025
2 votes

Google sheets returning correct value from json data

Enter this formula in cell I1: ={query({flatten(A1:G)},"select Col1 where Col1 contains 'current'"),query({flatten(A1:G)},"select Col1 where Col1 contains 'historic'")} Result ...
Tedinoz's user avatar
  • 5,751
2 votes
Accepted

How can I import json from url using only existing formulas of google sheets (without scripts or apps or whatever else additional there is)?

Use importdata() and extract numbers only by removing labels with regexreplace(), like this: =lambda( data, numRows, numColumns, query( makearray( numRows, numColumns, lambda( ...
doubleunary's user avatar
1 vote
Accepted

Trying to open an attachment in Slack: `{"ok":false,"error":"team_not_found"}` when clicking on "see it in full"

One can use the other download button: interestingly, this one works:
Franck Dernoncourt's user avatar
1 vote

Splitting JSON-Formatted Single Column Into Rows and Columns in Google Sheets

Another solution: Place your original IMPORTDATA formula in A1 of some sheet. Hide Col A if you like. Then in the top cell of some other column (say, Col B), you can place the following formula: =...
Erik Tyler's user avatar
  • 5,068
1 vote
Accepted

Splitting JSON-Formatted Single Column Into Rows and Columns in Google Sheets

Split the data before applying regexextract(), like this: =arrayformula( iferror( regexextract( transpose( split( query( transpose( importdata("...
doubleunary's user avatar
1 vote

Converting Google Sheet to JSON format while rows are vertical

This isn't a substitute for code that makes "JSON "properly, but it is a quick-and-dirty approach that does create valid JSON. Enter this formula in cell in row1. =ArrayFormula(concatenate(&...
Tedinoz's user avatar
  • 5,751
1 vote
Accepted

Parsing a non-nested JSON string in Google Sheets using formulas only

Assuming that the value of rev2_1 always will have 5 characters use =mid(A1,find("rev2_1",A1)+10,5) Explanation find finds the position of the first character of rev2_1 mid gets a text (substring) ...
Rubén - Volunteer Moderator -'s user avatar
1 vote

Parsing a non-nested JSON string in Google Sheets using formulas only

short formulas: =REGEXEXTRACT(A1;"_1""\: ""(.{5})") =REGEXEXTRACT(A1;"_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.{5})") =REGEXEXTRACT(A1;"rev2_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(...
user0's user avatar
  • 26.8k
1 vote

Parsing a non-nested JSON string in Google Sheets using formulas only

There might be a shorter way, such as with string manipulation (e.g., MID) or regular expressions (REGEXEXTRACT), but this works: =1*trim(substitute(substitute(substitute(index(SPLIT(A1,":"),3),"...
pnuts's user avatar
  • 17.7k

Only top scored, non community-wiki answers of a minimum length are eligible