Zapier 从 Tsheets 数据在 Google Sheet 中创建多行 [英] Zapier to create multiple rows in Google Sheet from Tsheets data
问题描述
在 Zapier Zap 中，我使用对 Tsheets 的 API GET 调用来获取时间表列表.我想将每个时间表拆分为行项目，例如 Xero 发票中的行项目，因为我想将每个时间表中的项目数据保存到 Google 工作表中的自己行中.(理想情况下，我想将行数据直接保存到 MySQL 数据库，但我看到 Zapier 目前仅支持一次保存多行的 Google 工作表.)但是我并不高兴.我怀疑以下两个问题之一:
In a Zapier Zap I am using an an API GET call to Tsheets to grab a list of Timesheets. I would like to split out each time sheet into line items like line items in a Xero invoice because I would like to save item data from each timesheet to its own row in a Google sheet. (Ideally I would like to save the line data directly to a MySQL database but I see that Zapier currently only support Google sheets saving multiple lines at a time.) However I am having no joy. I suspect one of two issues:
- Zapier 期望响应中包含单词 lineitems 或
- 响应的格式不正确 - 我似乎有两个结果"类别
在我设置 Google Sheets 电子表格行的步骤中，我没有选择逗号分隔的项目，如此处图片所示的示例所示:添加支持line items的action app，每个item都会单独保存图片来自此页面:https://zapier.com/blog/formatter-line-item-automation/ 标题为添加支持订单项的操作应用程序，每个项目将单独保存"有关我得到的信息，请参见照片 https://cdn.zapier.com/storage/photos/f055dcf11a4b11b86f9172f92323在从 API 返回数据的步骤中，文本响应显示在 https://cdn.zapier.com/storage/photos/33129fb7425cfae44be4a81533d6e892.png如果我返回 json 数据，它是这样的:https://cdn.zapier.com/storage/photos/34da1b98f8941324c35befef8efe350d.png
In my step to Set up Google Sheets Spreadsheet Row I don't get a selection of comma separated items as shown in the example shown on the picture here: Add an action app that supports line items, and each item will be saved individually The image is from this page: https://zapier.com/blog/formatter-line-item-automation/ with the caption "Add an action app that supports line items, and each item will be saved individually" For what I get see photo https://cdn.zapier.com/storage/photos/f055dcf11a4b11b86f912f9032780429.png In the step that returns the data from the API the text response is shown in https://cdn.zapier.com/storage/photos/33129fb7425cfae44be4a81533d6e892.png and if I return json data it is like this: https://cdn.zapier.com/storage/photos/34da1b98f8941324c35befef8efe350d.png
谁能确认我的怀疑是正确的，以及 1 还是 2 可能是罪魁祸首.
Can anyone confirm that my suspicions are correct and whether 1 or 2 is the likely culprit.
这个链接可以吗Zapier - Catch Hook - JSON Array - 遍历数组中的每一项 会引导我找到解决方案吗?看起来可能，但我不明白作者是如何将它融入到他的 Zap 中的.
Is it possible this link Zapier - Catch Hook - JSON Array - Loop over each item in array will lead me to the solution? It looks like it may but I don't see exactly how the writer incorporated it in to his Zap.
我从 API 返回的数据如下所示:
My data returned from the API looks like this:
{
"results": {
"timesheets": {
"11515534": {
"id": 11515534,
"user_id": 1260679,
"jobcode_id": 11974818,
"start": "2018-07-13T14:58:00+10:00",
"end": "2018-07-13T14:58:00+10:00",
"duration": 0,
"date": "2018-07-13",
"tz": 10,
"tz_str": "Australia\/Brisbane",
"type": "regular",
"location": "(Brisbane, Queensland, AU?)",
"on_the_clock": false,
"locked": 0,
"notes": "",
"customfields": {
"118516": "",
"121680": "",
"118530": "",
"118518": "Field supplies, materials"
},
"last_modified": "2018-07-13T04:59:27+00:00",
"attached_files": [
]
},
"11515652": {
"id": 11515652,
"user_id": 1260679,
"jobcode_id": 11974830,
"start": "2018-07-13T14:59:00+10:00",
"end": "2018-07-13T14:59:00+10:00",
"duration": 0,
"date": "2018-07-13",
"tz": 10,
"tz_str": "Australia\/Brisbane",
"type": "regular",
"location": "(Brisbane, Queensland, AU?)",
"on_the_clock": false,
"locked": 0,
"notes": "",
"customfields": {
"118516": "",
"121680": "",
"118530": ""
},
"last_modified": "2018-07-13T05:00:30+00:00",
"attached_files": [
]
},
"39799840": {
"id": 39799840,
"user_id": 1260679,
"jobcode_id": 19280104,
"start": "2018-10-24T11:45:00+11:00",
"end": "2018-10-24T12:00:00+11:00",
"duration": 900,
"date": "2018-10-24",
"tz": 11,
"tz_str": "Australia\/Brisbane",
"type": "regular",
"location": "(Sydney, New South Wales, AU?)",
"on_the_clock": false,
"locked": 0,
"notes": "",
"customfields": {
"118516": "",
"121680": "FP - Field plant Installation",
"118530": "Site cleanup"
},
"last_modified": "2018-10-24T05:56:27+00:00",
"attached_files": [
]
},
"39801850": {
"id": 39801850,
"user_id": 1260679,
"jobcode_id": 19280204,
"start": "2018-10-24T12:00:00+11:00",
"end": "2018-10-24T13:45:00+11:00",
"duration": 6300,
"date": "2018-10-24",
"tz": 11,
"tz_str": "Australia\/Brisbane",
"type": "regular",
"location": "(Sydney, New South Wales, AU?)",
"on_the_clock": false,
"locked": 0,
"notes": "",
"customfields": {
"118516": "",
"121680": "OP - Plant, Vehicles",
"118530": "Load\/Unload"
},
"last_modified": "2018-10-24T05:57:04+00:00",
"attached_files": [
]
},
"40192757": {
"id": 40192757,
"user_id": 1260679,
"jobcode_id": 19280110,
"start": "2018-10-25T08:00:00+11:00",
"end": "2018-10-25T10:00:00+11:00",
"duration": 7200,
"date": "2018-10-25",
"tz": 11,
"tz_str": "Australia\/Brisbane",
"type": "regular",
"location": "TSheets Android App",
"on_the_clock": false,
"locked": 0,
"notes": "From my mobile",
"customfields": {
"118516": "",
"121680": "FW - Plant Assembly",
"118530": "Panels"
},
"last_modified": "2018-10-24T23:02:56+00:00",
"attached_files": [
]
},
"40193033": {
"id": 40193033,
"user_id": 1260679,
"jobcode_id": 19280108,
"start": "2018-10-25T10:00:00+11:00",
"end": "2018-10-25T10:00:00+11:00",
"duration": 0,
"date": "2018-10-25",
"tz": 11,
"tz_str": "Australia\/Brisbane",
"type": "regular",
"location": "TSheets Android App",
"on_the_clock": false,
"locked": 0,
"notes": "",
"customfields": {
"118516": "",
"121680": "FW - Plant Assembly",
"118530": "Panels"
},
"last_modified": "2018-10-24T23:06:05+00:00",
"attached_files": [
]
}
}
},
"more": false
}
这是我的 Python 代码:https://imgur.com/a/8W1X1em
And this is my Python code: https://imgur.com/a/8W1X1em
推荐答案
好吧，我想我已经为您解决了一些问题.您提供的示例 Zapier -Catch Hook - JSON Array - Loop over each item in array 绝对是正确的，但是，因为它依赖于 webhooks，除非你可以从你的发票应用程序 POST 数据，否则它可能不会为你工作.
Alright so I think I've worked something out for you. The example you provided Zapier - Catch Hook - JSON Array - Loop over each item in array is definitely on the right track, but, because it relies on webhooks, it probably won't work for you unless you can POST the data from your invoicing application.
注意:我使用 Python 编写代码，因此我的示例将使用 Python，也就是说这些示例几乎与代码无关，也可以在 Javascript 中复制.
我设置了一个虚拟的 Zap 来复制当前你的 zap 发生的事情
Note: I code in Python so my examples will be in Python, that said these examples are pretty much code agnostic and can be replicated in Javascript as well.
I setup a dummy Zap to replicate what is happening with your zap currently
# results = requests.get(url, headers=header)
# results = results.json()
# Dummy result data converted to JSON object after API GET request:
results = {
"results" : {
"timesheets" : {
"timesheet_id_1" : {
"data_1" : "data",
"data_2" : "data",
"data_3" : "data"
},
"timesheet_id_2" : {
"data_1" : "data",
"data_2" : "data",
"data_3" : "data"
},
"timesheet_id_3" : {
"data_1" : "data",
"data_2" : "data",
"data_3" : "data"
}
}
}
}
return results
进一步阅读此处，以便 Zapier 映射订单项需要接收数组中的数据.上面的输出是一个字典对象，Zapier 确实将该字典中的值映射到以后可以访问的数据，但是它映射了整个字典，这就是为什么您将输出视为多个字段并在我的 输出.您要做的是映射字典的一个子集AND将每个子集作为单独的输出提供.
Reading a bit further here in order for Zapier to map line items it needs to receive the data in an array. The above output is a dictionary object, Zapier does map the values in this dictionary to data that can be accessed later, however it maps the entire dictionary which is why you are seeing the output as multiple fields and as is replicated in my output. What you are looking to do is map a subset of the dictionary AND provide each subset as separate outputs.
您要做的是遍历结果字典对象的内部字段并在嵌套的timesheet_id_n"上执行 zaps.为此，我们必须返回一个行项目列表，如上所述，行项目必须放入一个数组中.所以我实现这一目标的代码如下:
What you will want to do is loop through the inner fields of the results dictionary object and execute zaps on the nested "timesheet_id_n". To do so we will have to return a list of line items, as stated above line items must be placed into an array. And so my code to achieve this looks like:
# results = requests.get(url, headers=header)
# results = results.json()
# Dummy result data converted to JSON object after API GET request:
results = {
"results" : {
"timesheets" : {
"timesheet_id_1" : {
"data_1" : "data",
"data_2" : "data",
"data_3" : "data"
},
"timesheet_id_2" : {
"data_1" : "data",
"data_2" : "data",
"data_3" : "data"
},
"timesheet_id_3" : {
"data_1" : "data",
"data_2" : "data",
"data_3" : "data"
}
}
}
}
# Container for my line items. Each element in this list will be executed on separately
return_results = []
results = results.get("results")
results = results.get("timesheets")
for item in results:
return_results.append({"sheet_id" : item, "sheet_data" : results.get(item)})
return return_results
return_results 的输出将是一个字典对象数组.由于这些字典对象在数组中，Zapier 会将它们视为行项目，另外因为每个行项目都是一个字典对象，因此 Zapier 会自动映射每个值，以便它们可以在以后的操作步骤中独立使用.您可以在以下屏幕截图中我的触发器 zap 的输出中看到这一点:
The output of return_results will be an array of dictionary objects. As these dictionary objects are in array Zapier will treat them as line items, additionally because each line item is a dictionary object Zapier will automatically map each value so that they can be independently be used in later action steps. You can see this demonstrated in the output of my trigger zap in the following screenshots:
希望这有帮助！
这篇关于Zapier 从 Tsheets 数据在 Google Sheet 中创建多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
