Automating Spreadsheet Updates from Trigger Data in Zapier

Can you please send me the link so I can fix it for you?

For some reason I can’t share the template, says there was an error and contact support.

Hi @alanar! 👋

Do the work requests that the Zap is fetching output the relevant details as line items?

If so, perhaps you could pass those line items to a Looping by Zapier action run through each line item individually. To find the corresponding row using a Lookup Spreadsheet Row action, then update it using an Update Spreadsheet Row action. You can learn more about using loops here: Loop your Zap actions

Alternatively, there is a Lookup Spreadsheet Rows (output as Line Items) action that would allow you to search for multiple different rows the results of which would be output as line items. Then you could use the Update Spreadsheet Row(s) action to take the Row IDs for the rows it finds and then update multiple rows using the line item data from the trigger.

Do you think that could work? If I’ve misunderstood or you run into any trouble on that do let me know!

Hey there!

Thank you so much for your time and help, seems like you know the drill very well.

This is how the work requests output looks like (it’s multiple arrays by using “cath raw hook”, if I’ve used a different action like GET, Zapier would format it per line and I wasn’t able to retrieve the data properly). I’m just gonna paste a few since there’s a lot:

{"workRequests":[{"id":3052878,"assetId":null,"createdAt":"2024-02-11T07:55:37.779Z","creatorId":459080,"description":null,"locationId":null,"organizationId":176317,"priority":"NONE","requestStatus":"PENDING","title":"test new request","updatedAt":"2024-02-11T07:55:37.779Z"},{"id":3042932,"assetId":null,"createdAt":"2024-02-08T23:12:52.379Z","creatorId":459080,"description":null,"locationId":1324588,"organizationId":176317,"priority":"HIGH","requestStatus":"APPROVED","title":"Salio un sapo por el filtro de agua","updatedAt":"2024-02-08T23:13:46.640Z","workOrderId":24423117},{"id":3041238,"assetId":null,"createdAt":"2024-02-08T19:28:20.824Z","creatorId":459080,"description":null,"locationId":1324588,"organizationId":176317,"priority":"MEDIUM","requestStatus":"APPROVED","title":"Toilet blew up in the air","updatedAt":"2024-02-08T19:30:24.693Z","workOrderId":24410847}

After that output, I have two “get many spreadsheet rows” steps to bring the “workOrderId” values and “rowId” (on the GSheet). With those 3 outputs in place, I have a 4th JS step with the following code:

// Attempt to parse the inputData.workRequests, handling potential structure issues
let workRequests = [];
try {
    // Attempt to directly parse the inputData.workRequests assuming it might be a string
    const initialParse = JSON.parse(inputData.workRequests || '{}'); // Adjusting for potential string input
    // Check if 'workRequests' is a key in the parsed object
    workRequests = initialParse.workRequests ? initialParse.workRequests : [];
    console.log('Correctly parsed workRequests:', workRequests);
} catch (error) {
    // Log any errors encountered during parsing
    console.error('Error parsing workRequests:', error);
}

// Ensure inputData.sheetWorkOrderIds and inputData.sheetRowIds are correctly formatted as arrays
let sheetWorkOrderIds = [];
let sheetRowIds = [];
try {
    // Parse and flatten the sheetWorkOrderIds assuming they might be in an array of arrays
    sheetWorkOrderIds = JSON.parse(inputData.sheetWorkOrderIds || '[]').map(item => item[0]);
    console.log('Parsed sheetWorkOrderIds:', sheetWorkOrderIds);

    // Parse and flatten the sheetRowIds assuming they might be in an array of arrays
    sheetRowIds = JSON.parse(inputData.sheetRowIds || '[]').map(item => item[0]);
    console.log('Parsed sheetRowIds:', sheetRowIds);
} catch (error) {
    // Log any errors encountered during parsing
    console.error('Error parsing sheet data:', error);
}

// Create a mapping from workOrderId to rowId based on their index
let orderToRowMap = {};
sheetWorkOrderIds.forEach((id, index) => {
    orderToRowMap[id] = sheetRowIds[index];
});
console.log('orderToRowMap:', orderToRowMap);

// Filter for 'APPROVED' work requests and prepare their updates
const updates = workRequests.filter(req => req.requestStatus === "APPROVED").map(req => {
    const rowId = orderToRowMap[req.workOrderId];
    if (rowId) {
        // Construct the update object if a matching rowId was found
        return {
            rowId: rowId,
            workRequestId: req.id
        };
    }
    return null; // Return null for requests without a matching spreadsheet row
}).filter(update => update != null); // Filter out null values from the updates array
console.log('Correctly prepared updates:', updates);

// Additional debugging information
console.log('Number of APPROVED workRequests:', workRequests.filter(req => req.requestStatus === "APPROVED").length);
console.log('Number of updates prepared:', updates.length);

return { updates };

The output is the following:

updates
1
rowId
rowId
13
workRequestId
workRequestId
3042932
2
2
Updates Row Id
rowId
11
Updates Work Request Id
workRequestId
3041238
3
3
Updates Row Id
rowId
12
Updates Work Request Id
workRequestId
3034504

This basically finds which workRequestId (just “id” in the 1st step output) matches with the workOrderId and rowId from the Gsheet.
With that, I should update the Gsheet with the workRequestId promptly, but I’m kinda stuck and burned out by now.

Thanks for getting back to me @alanar.

If the Catch Hook trigger is sending them over as arrays then they’re likely Iine items then. Is the output from the Code action correct or is it not matching up the correct ID?

If it’s outputting the correct row ID number and corresponding workRequestId then you should be able to select the Row Id in the Row Number field on a Update Spreadsheet Row(s) action to specify the row that needs to be updated with the corresponding workRequestId that’s also output by the Code action.

If it’s not matching up the rows and workRequestId correctly then you might want to try using the Looping by Zapier approach instead. As that would allow you to loop actions to run for each line item it receives from the trigger.

• Trigger: Catch Hook (Webhooks by Zapier) - Outputs the relevant details for new work requests.
• Action: Create Loop From Line Items (Looping by Zapier) - creates a loop for each work request line item that carries out the following actions.
  • Action: Lookup Spreadsheet Row (Google Sheets) - searches for the corresponding row with the same workOrderId.
  • Action: Update Spreadsheet Row (Google Sheets) - updates the row found by the previous action with the relevant workRequestId.

Can you give the above a try and let me know how that goes?