2

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:

  1. Zapier expects the word lineitems in the response or
  2. The format of the response is not correct - I seem to have two "results" categories

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

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.

Edit: 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
}

And this is my Python code: https://i.stack.imgur.com/tZ1ZT.jpg

Karl In OZ
  • 63
  • 8

1 Answers1

4

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.

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

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.

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

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

Hope this helped!

Michael Case
  • 508
  • 4
  • 13
  • Michael thank you for your response it has got me a lot further than I had gotten previously. I have Python grabbing my data alas only the first data item is being parsed at present. I've edited my question to include my data returned from the API – Karl In OZ Oct 26 '18 at 05:38
  • At what point are you getting stuck at the moment? Are there any errors that are popping up? – Michael Case Oct 26 '18 at 06:59
  • No error, it all runs fine but only one record is returned: runtime_meta duration_ms 457 memory_used_mb 24 logs sheet_id 39785064 id 4ghY1Ra7WiCPEx6JW4oKe5pk9jD61vk5 sheet_data attached_files end tz_str Australia/Brisbane notes Added by IT Dept via PTO entry tool. jobcode_id 11767513 locked 0 on_the_clock false start last_modified 2018-10-23T23:43:17+00:00 location (Sydney, New South Wales, AU?) date 2018-10-21 user_id 1260679 customfields 118516 118530 121680 type manual id 39785064 duration 28800 tz 11 – Karl In OZ Oct 26 '18 at 09:42
  • Hmm, I was able to replicate the steps exactly and have it working for me. Have you turned the Zap on? When you are testing it will only show one response as output, when you turn it on though it will execute however many are in your return_list. I also see that you are using a formatter action as your following step. You may want to consider trying to incorporate that into your code step as you could save yourself some Zaps. – Michael Case Oct 26 '18 at 20:20
  • Aha I'm sure that will be the issue Michael - running it in test mode rather than turning the Zap on. Thanks again for all your help. I am on my weekend break at the moment so wont try it out till Monday. Yes I'm still in development mode with the zap and the following step will probably disappear for the final version. Thanks too for the tip to reduce the number of Zaps though. And another thing I learned from your example is how to include sample data in my Zaps for testing :) – Karl In OZ Oct 26 '18 at 21:21
  • And just for completeness: Michael, indeed, the problem was that I was running in test mode. Once I turned the Zap on your solution works perfectly well. And thanks too for the tip about using code instead of the formatter, I was able to eliminate thousands of tasks - I'm having to trigger once an hour using the Zapier scheduler as there is no Tsheets trigger. – Karl In OZ Nov 09 '18 at 03:45
  • And incidentally, using Michael's answer I was able to write directly to my MySQL database rather than having to use an intermediary Google sheet. – Karl In OZ Nov 09 '18 at 04:06