Hi,
I want to send the following JSON body in a web API. How can I do this in Triggre without needing to include the value names?
{
“values”: [
[
“12:00 AM”,
“Name Last”,
“Globe”,
“Company”,
“No Reason”,
“2000”,
“c:/”
]
]
}
Kind regards,
Melissa
Hi Melissa,
Currently it’s not possible to do that with Triggre. In general it’s quite strange for me to send it like this, since the order of the values will matter a lot.
Maybe it’s possible using the Webhook functionality of Zapier.
The issue here is not the ordering, but the fact dat Google Sheets API Append needs an array in an array.
We are looking for a way to accomplish this.
Unfortunately, in this use case Zapier is not an option.
Hi @ronald,
There currently doesn’t seem to be a compatible solution in Triggre indeed. We’ll look into this with the development team. It is a case we haven’t run into yet, nameless arrays inside another array. Or nameless values in general, for that matter.
That being said, I think there is a temporary solution:
Create a PHP script that can convert a JSON structure that Triggre can make, into one that is compatible with Google Sheets, sends it to Google Sheets (without needing to know which sheet etc, that can be sent as data to the script), and returns the exact same structure it gets back from Google to Triggre.
That way, when we have resolved the issue, you can remove this ‘proxy’ script and send data to Google Sheets directly, without having to change a lot in the design.
Would it be an option?
Hi @Jesse, a blast from the past. I was a bit curious if the “nameless array” functionality has been discussed with the dev team and if yes, what the outcome is.
We are still struggling with the fact that we can’t Append to an Excel (Google Sheet) via the API. The amount of records we need to export nightly (>10k) requires this approach.
Hi @ronald,
We discussed it but didn’t find a good solution just yet. For the Web API we have prioritized a few other important features, that allow better use of complex structures.
One of the issues is that we ‘collapse’ lists in lists to a single list. This is a feature that is a core part of the Triggre concept, so in order for this one very specific exception to work we need to really find a good workaround.
I will discuss it with the team again in the light of these new features, perhaps someone has a good idea of how to support this without compromising a core feature of the platform.
That being said I think there is a simple workaround possible as I described using PHP (or another scripting language like Python). Though I think you already use some form of workaround and would (obviously) prefer to see this work natively, it could be a very good solution for the time being.
A quick update on this is that we are researching a possible implementation that we came up with during a recent discussion on this topic.
Currently we are developing another important feature related to the Web API (as well as releasing an important Web API feature this week). After that development is done, we can probably pick this up.
Normally I don’t share our designs, but because this is such a specific case I would like to know if you think this will cover your case correctly. We think it does, so here goes (this one may be a long read 
)
Source
Just to be absolutely sure we are talking about the same source, we have used this Google sheets documentation page as input: REST Resource: spreadsheets.values | Google Sheets | Google for Developers.
It speaks about appending data using a list of lists, named values, using the append call documented here: Method: spreadsheets.values.append | Google Sheets | Google for Developers.
Data model
For the design, I have created a very simple data model consisting of a Row data item with the cells as properties:
Note that the properties we will use as cells can be of different types.
This model will allow us to create a list of lists by looking up all rows and then specifying the cells. This is important, because we want each list to have the same length and same types (which is why this design is preferred over a different design where each row item would have a list of cells with a value, for example).
Flow part
As an example, I have created a flow part Append all rows which first looks up all rows, then sends the to the API:
I will omit the lookup for all rows, and instead focus on the connection action. In this case, we want to add the values as an array of arrays (a matrix) to the JSON data by clicking the add button here:
First we need to add the values array to the flow part in the regular way. So the first step remains the same; we select a property from the flow part:
And in this case, we want to send the All rows property of the flow part as an array, so we select this property from the flow part:
This results in our outer values array, meaning each item in this array represents a row:
The next step is adding a value inside of the values array by clicking the add button. This currently shows the following options:
In our proposed solution we would add an option here, List from data item list:
This option is only available if there are no other properties added yet; because we don’t want to combine nameless arrays with named values.
This option would allow you to select multiple values from each Row and represent it as an array:
This would result in the JSON structure as discussed in this thread. Because we only allow either properties, or nameless arrays, no other fields can then be added to the values list.
Conclusion
We think that this solution should cover this specific case, plus allow the flexibility for multiple data types. It also makes sure that the resulting structure is a matrix, and not a list of lists where the inner lists can have different lengths.
Please let us know whether you feel this will indeed cover your case and perhaps other cases you have seen, or that it might be missing things.
