google sheet formula to get convert two column table into JSON and use display value instead of cell value

0
votes

Example sheet: https://docs.google.com/spreadsheets/d/1KLAB9PTGQfKJ4XWTVFScNyhlFkZSKcrMRWn4z0wGKZU/edit?usp=sharing

I have a table with a variable number of rows and two columns. I want to convert the table to a JSON string with the first column being the key and second column being the value.

I have a formula but it uses the cell value and not the cell display value. For example, in my above example sheet, it outputs:

  • "number" : "1" instead of "number" : "1.0"
  • "date" : "4378" instead of "date" : "2019-11-15"

enter image description here

Is there a way to tell the formula to use the cell display value and not the cell value?

1
google-sheets

1 Answers

1
votes

Convert the second column to text: 

="{" & TEXTJOIN(", ", TRUE, ARRAYFORMULA(IF(A2:A<>"", """" & A2:A & """ : """ & TO_TEXT(B2:B) & """", ""))) & "}"

and see if that works?