Search code examples
excelsharepoint-onlinezapier

Can you create a row in Excel using Zapier that includes calculated cells and then retrieve those values?

We use a spreadsheet to generate incremental numbers based on a variable. The variable has 4 different options and each one coincides with a numerical prefix. The first two representing the year.

Option/Prefix

  1. 220 (220000,220001,220002 etc.)
  2. 227
  3. 228
  4. 229

There are 4 columns one for each option. When you insert your initials in the correct column, the corresponding number column is incremented by 1 and the correct number is returned. Normally the cells at the right are hidden enter image description here

This was part of my effort to eliminate duplicate values from being used by accident but it still happens at times so I am trying to set it up to have Zapier monitor our CRM and when it reads a record calling for new number, it will add text to the table in order to generate the number and then retrieve it and insert it into the record in the CRM.

The spreadsheet is on Sharepoint online.

  • Will formulas be calculated when the Table is updated by Zapier?
  • How do I find the row afterwards and return the value to Zapier?

When I tried to test the 'find row' action using a value that is the result of a formula the search fails but if it is entered text, the search works.

Solution

  • First to answer the original question, I put the same question to Zapier support and they responded that I might get cached data if I access the file more than once in a short period. So I sought an alternative and came up with this solution:

    The process begins with a dropdown in the CRM where the user selects the type of number they require (Trigger Value - TV).

    The Zap polls the CRM for changes and is filtered to continue only if a TV exists.

    I ended up creating a helper column with the word 'zap' in every cell from the target row and below (A). I also added a column to hold the TV (C) and the zap will updates this column later on. I modified the cells on the right to fill every row with the highest value in each column (I:L) using this formula:

    The first part is only to stop the numbers from displaying beyond the target row. The target row is the row in green and is located below the final values. (Row ID:536 in this example) enter image description here

    This is the row we want the Zap to find. The lookup value in the 'Find Row Action' is 'zap' and the lookup column is '1'.

    In this example, the next row that the Zap will find and return is 536. Now, using a Formatter Action (Text), I entered this formula: enter image description here

    Based on the TV retrieved from the CRM, the Formatter will return the value from the corresponding column and increment it by 1. This value will then be returned to the CRM.

    The next step is to update the Target Row (ID:536 in this example) in Excel.
    It updates Column A (or 1) with the formula ="" which removes the word 'zap' and Column C (or 3) is updated with the TV from the CRM. Now the sheet is ready for the next run.

    The last thing is to update the record in the CRM by deleting the TV.

    The middle columns (D:G) are not required for the Zap. They are there just to make it easier to see the results if you have to view the file.

    In a given year, we will use less than 1500 rows so I copied the formulas down to row 2000. Since the first two digits are based on the year, and because VBA will not run when the hosted file is opened, it will unfortunately require manual intervention to reset the spreadsheet at the beginning of each year. The drawback is that it uses 6 tasks for each number retrieved but at least we won't end up with duplicate numbers being used due to human error.