Hello fellow Makers!
Google Sheets has an “append” endpoint you could use to add multiple rows in a single call, thus saving tons of Ops, but it’s difficult to use.
Given a data set like this:
Summary
[
{
"0": "Header Col A",
"1": "Header Col B",
"2": "Header Col C",
"__ROW_NUMBER__": 1,
"__SHEET__": "Sample Data",
"__IMTLENGTH__": 4,
"__IMTINDEX__": 1
},
{
"0": "Row 2 Col A",
"1": "Row 2 Col B",
"2": "Row 2 Col C",
"__ROW_NUMBER__": 2,
"__SHEET__": "Sample Data",
"__IMTLENGTH__": 4,
"__IMTINDEX__": 2
},
{
"0": "Row 3 Col A",
"1": "Row 3 Col B",
"2": "Row 3 Col C",
"__ROW_NUMBER__": 3,
"__SHEET__": "Sample Data",
"__IMTLENGTH__": 4,
"__IMTINDEX__": 3
},
{
"0": "Row 4 Col A",
"1": "Row 4 Col B",
"2": "Row 4 Col C",
"__ROW_NUMBER__": 4,
"__SHEET__": "Sample Data",
"__IMTLENGTH__": 4,
"__IMTINDEX__": 4
}
]
The goal is to get a JSON formatted like this:
{
"values": [
[
"Header Col A",
"Header Col B",
"Header Col C"
],
[
"Row 2 Col A",
"Row 2 Col B",
"Row 2 Col C"
],
[
"Row 3 Col A",
"Row 3 Col B",
"Row 3 Col C"
],
[
"Row 4 Col A",
"Row 4 Col B",
"Row 4 Col C"
]
]
}
The result of a Parse JSON with the above JSON looks like this:
Summary
And the underlying JSON is basically the same but it’s just wrapped up in an array of 1 element:
Summary
[
{
"values": [
[
"Header Col A",
"Header Col B",
"Header Col C"
],
[
"Row 2 Col A",
"Row 2 Col B",
"Row 2 Col C"
],
[
"Row 3 Col A",
"Row 3 Col B",
"Row 3 Col C"
],
[
"Row 4 Col A",
"Row 4 Col B",
"Row 4 Col C"
]
]
}
]
I know that given a Text Aggregator set up like this:
I can get this result:
["Row 2 Col A (Data, "first" row)","Row 2 Col B","Row 2 Col C"],
["Row 3 Col A","Row 3 Col B","Row 3 Col C"],
["Row 4 Col A (Data, last\row)","Row 4 Col B","Row 4 Col C"]
Once I wrap the result with a “values” key and brackets, something like this:
{ "values": [
["Header Col A","Header Col B","Header Col C"],
["Row 2 Col A (Data, "first" row)","Row 2 Col B","Row 2 Col C"],
["Row 3 Col A","Row 3 Col B","Row 3 Col C"],
["Row 4 Col A (Data, last\row)","Row 4 Col B","Row 4 Col C"]
]
}
If not for the double quotes or commas in the data, this would work great.
The problem is that if the data contains double quotes or commas (or other unescaped special characters), they are not escaped properly so it’s not valid JSON.
I could go find every double quote or backslash and escape them, which would make my Text Aggregator look like this:
But as the number of columns grows, so does the complexity of these functions.
To be clear, I want this to work with a single JSON Aggregator, but not sure how to do it or if this is even possible.
If I use a Match Pattern/Replace module, that will run an operation on each value.
If I use nested Iterators and Aggregators to build the results each row at a time, that uses one Op for each row.
I was hoping to find the correct Data Structure set up that will result in properly escaped values.
The closest I’ve been able to get was this structure, where “values” is an array of array of text.
The result is individual “values” objects:
[
{"values":[["Header Col A"],["Header Col B"],["Header Col C"]]},
{"values":[["Row 2 Col A (Data, \"first\" row)"],["Row 2 Col B"],["Row 2 Col C"]]},
{"values":[["Row 3 Col A"],["Row 3 Col B"],["Row 3 Col C"]]},
{"values":[["Row 4 Col A (Data, last\\row)"],["Row 4 Col B"],["Row 4 Col C"]]}
]
Does anyone know how to build the JSON aggregator’s structure correctly, or transform the above result into the correct structure, or any other way to make this happen?
I know with a series of clever text replacements we can transform the above into what is needed (assuming the data doesn’t contains any text that gets replaced, which is still a long shot), but any other more direct ideas?
Summary
Replace ],[ with ,
Replace ]},{"values":[ with ,
Replace }] with }
Replace [{ with {
Here is a blueprint showing the 3 paths I’ve worked through, two Text Aggregators (one using replace() functions to escape characters, one without, and the JSON aggregator not producing the results I need.
36985.json (24.3 KB)
Appreciate any feedback!
