How can I use Zapier to create a new row in Excel with calculated fields and subsequently retrieve those values?

Other Questions Zapier
1

I manage a spreadsheet that produces incremental numbers based on a variable with four distinct choices, each corresponding to a specific numeric prefix. The first two digits represent the year. Here’s a breakdown of the options and their respective prefixes:

  • 220 (examples: 220000, 220001, 220002, etc.)
  • 227
  • 228
  • 229

The spreadsheet has four columns, one for each option. When initials are entered in the appropriate column, the relevant number in that column is incremented by one, producing the correct number. Although I have hidden the cells on the right to prevent accidental duplicates, mistakes still occur. I would like to configure Zapier to monitor our CRM. When it detects a request for a new number, I want it to add information to the spreadsheet to generate the number, retrieve it, and then insert this value into the CRM record.

The spreadsheet is hosted on SharePoint Online. Here are my questions:

  1. Will the formulas recalculate when Zapier updates the table?
  2. How can I locate the row afterwards and return that value to Zapier?

I’ve encountered an issue where using a formula result for the ‘find row’ action fails to yield results, while manually entered text works fine.

2

you might wanna check Zapier’s ‘Lookup Spreadsheet Row’ feature. After adding your info, use it to locate the specific row. sometimes recalculation issues arise due to caching, so make sure to refresh or use an
updated connection. just keep that in mind when setting it up. cheers!

3

Another approach is to use a unique identifier within your Zap. Once you add new data to your Excel sheet through Zapier, you can use that unique identifier as a reference point when retrieving calculated fields back to the CRM. This identifier could be included in both the input data and as a column in your spreadsheet for easy matching. Ensure your spreadsheet is structured properly to reflect any updates efficiently, and test the workflow thoroughly to ensure accuracy in retrieval and recalculation.