Zapier从Tsheets数据在Google表格中创建多行 [英] 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表格电子表格行的步骤中，我没有选择用逗号分隔的项目，如下面图片所示的示例所示: 添加支持订单项的操作应用，并且每个项目将单独保存 图片来自此页面: https://zapier.com/blog/formatter- line-item-automation/的标题为添加支持订单项的动作应用，并且每个项目将单独保存".有关我得到的信息，请参见照片 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.
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-捕获钩子-JSON数组-遍历数组中的每个项目绝对是正确的方法，但是，由于它依赖于webhooks，因此除非您可以从发票应用程序中发布数据，否则它可能对您不起作用.
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确实将该字典中的值映射到以后可以访问的数据，但是它映射了整个字典，这就是为什么您将输出视为多个字段并在我的输出.您要做的是映射词典的一个子集，并且将每个子集作为单独的输出提供.
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:
output 1
output 2
output 3
希望这对您有帮助！
这篇关于Zapier从Tsheets数据在Google表格中创建多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
