Hello,
I am trying to create a spreedsheet in Google Sheets from a template. When using it without mapping the data, it works perfectly (see attached picture).
However, since i have more then 20 datasets that i want to put into the sheet, im trying to map it. This does not work because im not sure what to put into the [object Object] field. What syntax is used here?
I was not able to find any documentation on this.
Thank you for your help in advance.
You have not yet provided sufficient information to demonstrate the problem that would allow us to reproduce the scenario and any error(s).
To allow others to assist you with your scenario, please provide the following:
Could you please share screenshots of your full scenario? Also include screenshots of any error messages, module settings (fields), relevant filter settings (conditions), and module output bundles. We need to see what you’re working with to give you the best advice.
You can upload images here using the Upload icon in the text editor:
We would appreciate it if you could upload screenshots here instead of linking to them outside of the forum. This allows us to zoom in on the image when clicked, and prevent tracking cookies from third-party websites.
Please export the scenario blueprint. Providing your scenario blueprint file will allow others to quickly recreate and see how you have set up the mappings in each module, and also allows us take screenshots or provide module exports of any solutions we have for you in return - this would greatly benefit you in implementing our suggestions as you can simply paste module exports back into your scenario editor!
To export your scenario blueprint, click the three dots at the bottom of the editor then choose ‘Export Blueprint’.
You can upload the file here by clicking on this button:
Please provide the output bundles of each of the relevant modules by running the scenario (you can also get this without re-running your scenario from the History tab).
Click on the white speech bubbles on the top-right of each module and select “Download input/output bundles”.
Save each bundle contents in a plain text editor (without formatting) as a bundle.txt file.
You can upload the file here by clicking on this button:
If you are unable to upload files on this forum, alternatively you can paste the formatted bundles.
These are the two ways to format text so that it won’t be modified by the forum:
Method 1: Type code block manually
Add three backticks ``` before and after the content/bundle, like this:
```
content goes here
```
Method 2. Highlight and click the format button in the editor
Providing the input/output bundles will allow others to replicate what is going on in the scenario, especially if there are complex data structures (nested arrays and collections) or if external services are involved, and help you with mapping the raw property names from collections.
Sharing these details will make it easier for others to assist you.
Hello @samliew
thank you for your reply. I have attached the blueprint and a screenshot of the scenario and output bundle that i want to add.
Here is the bundle content:
[
{
"Gesamtwohnflaeche_Objekt": "987",
"Flaeche_1-1D": "1401",
"Gesamtwohnflaeche_Anlage": "1782",
"Gesamt_Personenzahl": "21",
"Grundsteuer": "3095.58",
"Frischwasser": "1069.90",
"Schmutzwasser": "1069.90",
"Niederabschlagswasser": "2263.26",
"Strassenreinigung": "119.46",
"Müllbeseitigung": "2544.38",
"Gebaudereinigung": "0",
"Dachrinnenreinigung": "150,00€",
"Aussenanlagekosten": "150,00€",
"Allgemeinstrom": "2229.94",
"Schornsteinfegerkosten": "100.34",
"Gebaudehaftpflichtversicherung": "252.33",
"Wohngebaudeversicherung": "4350.58",
"Winterdienst": "150,00€",
"Heizungswartung": "239.70",
"Heizkosten_abrechnung": "12112.63"
}
]
I am trying to add this data to the “create spreedsheet from template”-module and map the input.
blueprint (2).json (179.4 KB)
Hello,
does someone have an idea how to do this? I don’t want to put in 50 parameters by hand.
Sorry for the delay in response – my schedule was booked out with private clients for the past two weeks, and I couldn’t spend too much time on the forum.
To answer your question, here is my observation — you probably do not need a Parse JSON module. Just point the Array Aggregator’s “Target Structure Type” to the “Values” field of the Sheets module.
Every result (item/record) from trigger/iterator/list/search/match modules will output a bundle. This can result in multiple bundles, which then trigger multiple operations in future modules (one operation per bundle). To “combine” multiple bundles into a single variable, you’ll need to use an aggregator of some sort.
Aggregators are modules that accumulate multiple bundles into one single bundle. An example of a commonly-used aggregator module is the Array aggregator module. The next popular aggregator is the Text Aggregator which is very flexible and can apply to many use-cases like building of JSON, CSV, HTML.
You can find out more about the other types of aggregator modules here:
Question: Which is the best aggregator do you think you’ll need for your use-case?
If you have an array of collections, in programming terms, this is called an array of objects, or an array with non-primitive data types (“complex”).
The Array Aggregator module is very powerful because it allows you to build a new complex array of collections that matches a later module’s field to map multiple items (collections) to it. Such fields initially would allow you to manually add items, but you can toggle the “Map” switch to the “on” state and map a whole array into a single field.
This is done by selecting the “Target structure type” in an Array Aggregator module.
As you can see from the above example, the “Map” toggle on complex fields are used when you have an array variable (like from an array aggregator). Other combinations of modules may also allow you to generate an array that matches a future field’s array structure, like “Aggregate to JSON + Parse JSON”, or “Create JSON + Parse JSON”, but this is an advanced topic.
Question: Are you mapping your array into a field that accepts more than one item/collection?
Here is an example of how your scenario could look like:
This is just an example. Your final solution may or may not look like this depending on your requirements and actual data.
For more information, see “Mapping with arrays” in the Help Centre. You should also do the Make Academy, which also covers the use of Iterators & Aggregators.
Hope this helps! Let me know if there are any further questions or issues.
— @samliew
Hello Samliew,
thank you for your very detailed answer. I really appreciate you taking time to help me.
I tried to put the Array as the input in the Sheets module. However, this still does not work. I have attached a picture of the error message and a picture of the output of the bundle that i get in the array. Maybe you can have a look at this?
I would love to hear from you.
Hello again,
i kind of figured this out.
So I did not find out how to do this with the google Sheets module, but I did find a workaround:
I use 0CodeKit to run some Python-Code. With this python-code i then do an api-call to google sheets.
My Code in the 0codekit module looks like this:
import requests
spreadsheet_id = “{{36.id}}”
access_token = “censored”
input_array = {{28.array}}
open_braces = chr(123) + chr(123)
close_braces = chr(125) + chr(125)
placeholders =
for item in input_array:
key = item.get(“0”)
value = item.get(“1”)
if key and value is not None:
placeholders.append({
“placeholder”: open_braces + key + close_braces,
“value”: str(value)
})
requests_body = [
{
“findReplace”: {
“find”: entry[“placeholder”],
“replacement”: entry[“value”],
“allSheets”: True
}
}
for entry in placeholders
]
url = f"https://sheets.googleapis.com/v4/spreadsheets/{spreadsheet_id}:batchUpdate"
response = requests.post(
url,
headers={
“Authorization”: f"Bearer {access_token}",
“Content-Type”: “application/json”
},
json={“requests”: requests_body}
)
result = {
“data”: {
“status_code”: response.status_code,
“response”: response.json()
}
}
Thank you for your help and time.
