Automating Spreadsheet Updates from Trigger Data in Zapier

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

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.