How to insert individual timesheet records into Google Sheets rows using Zapier automation

Other Questions Zapier
1

I’m working on a Zapier workflow that pulls timesheet data from an API and I need to create separate Google Sheets rows for each timesheet entry. Right now I’m making a GET request to fetch multiple timesheets, but I want each individual timesheet to become its own row in the spreadsheet.

I think the problem might be one of these issues:

  1. Maybe Zapier needs a specific field name in the API response
  2. The JSON structure I’m getting back has nested data that’s confusing Zapier

When I try to set up the Google Sheets step, I don’t see the option to split the data into multiple rows like I expected. Instead of getting comma-separated values for each item, I just see the whole response as one block.

Here’s what my API returns:

{
  "data": {
    "work_logs": {
      "12345678": {
        "record_id": 12345678,
        "employee_id": 987654,
        "project_id": 55512233,
        "clock_in": "2023-08-15T09:00:00+10:00",
        "clock_out": "2023-08-15T17:30:00+10:00",
        "total_time": 30600,
        "work_date": "2023-08-15",
        "timezone": 10,
        "region": "Australia/Sydney",
        "shift_type": "standard",
        "work_location": "Office Building A",
        "status": "completed",
        "is_locked": 0,
        "comments": "Regular shift",
        "custom_data": {
          "dept_code": "ENG001",
          "task_type": "Development",
          "priority": "High"
        }
      },
      "12345679": {
        "record_id": 12345679,
        "employee_id": 987654,
        "project_id": 55512234,
        "clock_in": "2023-08-16T08:30:00+10:00",
        "clock_out": "2023-08-16T16:45:00+10:00",
        "total_time": 29700,
        "work_date": "2023-08-16",
        "timezone": 10,
        "region": "Australia/Sydney",
        "shift_type": "standard",
        "work_location": "Remote",
        "status": "completed",
        "is_locked": 0,
        "comments": "Work from home",
        "custom_data": {
          "dept_code": "ENG002",
          "task_type": "Testing",
          "priority": "Medium"
        }
      }
    }
  },
  "has_more": false
}

Does anyone know if there’s a specific way to format the response so Zapier recognizes it as separate line items? I want each work log to create its own row in the Google Sheet with all the individual fields mapped properly.

2

The main problem is that your API returns work logs as an object with dynamic keys instead of an array. Zapier expects arrays for line item processing, not objects. What worked for me was using Webhooks by Zapier to catch the data, then a Code by Zapier step with this JavaScript snippet: const workLogs = Object.values(inputData.data.work_logs); return workLogs.map(log => ({line_item: log})); This converts your object structure into proper line items that Zapier can iterate over. After that, the Google Sheets step should automatically create separate rows for each work log entry. Just remember to map the fields correctly in the Sheets action since they’ll be nested under the line_item property.

3

yeah, zapier struggles with nested data. use a Code by Zapier step b4 sending to sheets! it’ll let u loop through the work_logs and make em flat, so each one gets its own row. good luck!

4

The issue you’re facing is that Zapier doesn’t automatically iterate over object properties like your work_logs structure. Since your API returns work logs as an object with dynamic keys rather than an array, Zapier treats the entire response as a single item.

You’ll need to add a Looping by Zapier step after your webhook trigger. First, use a Code by Zapier step to extract the work_logs object and convert it into an array format that the Looping step can process. In the code step, iterate through Object.values(inputData.data.work_logs) and return each work log as a separate line item.

Once you have the looping set up, each iteration will trigger the Google Sheets action separately, creating individual rows for each timesheet record. Make sure to test thoroughly since looping can consume multiple Zap runs depending on how many records you’re processing at once.

5

actually had same prob last month. zapier’s formatter step can help too - use the utilities option to extract values from that nested json before the sheets action. way simpler than coding imo and works great for this kinda structure

6

Been dealing with similar timesheet integrations and honestly the object structure you’re getting is pretty common but annoying for Zapier. What I found works well is adding a Paths by Zapier step right after your webhook - it helps handle the branching logic better than just jumping straight to code.

In your case though, since you have that nested custom_data section, you might want to flatten those fields first before the looping happens. Otherwise you’ll end up with [object Object] in your sheets instead of the actual department codes and task types. Use Object.assign() in your code step to merge the custom_data properties with the main log properties before creating the line items.

Also worth noting that your total_time field is in seconds, so you might want to convert that to hours during the transformation step to make the spreadsheet more readable. Makes reporting much easier down the line.