Best answer

Inserting Row into Excel but skip first column

  • 28 April 2021
  • 6 replies
  • 380 views

Userlevel 1

Hi there

I am new to Zapier and just creating my first project.  I have searched the forum for an answer with no success so forgive me if this is very basic stuff.

I am already successfully retrieving data from a FastField form and inserting it into a customers existing Excel file. The data is then stored in the Excel file against a prefined job no which already exists in column A.  Is there a method of inserting the retrieved data whilst leaving the job number in column A?

So in summary, I would like to add data from FastField into cells B2, C2, D2 & E2 whilst leaving the Job number in Column A.  The next record received would then be stored in B3, C3, D3 & E3 etc.

Many thanks.

icon

Best answer by Troy Tessalone 28 April 2021, 16:13

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

6 replies

Userlevel 1

Hi Troy,

My mistake, I was still trying to insert instead of updating the row.  This is now working perfectly thank you for your help. :ok_hand:

Userlevel 7
Badge +12

@Acorn_UK 

It is possible with the update row action. However, you will need to implement a search step before the update action so that you can dynamically find the last row. There are lots of ways to do this, one way would be do insert a static value in a ‘lookup column’ every time you update a row. Then you can search for that value and add 1 to the row returned which would be the next row. 

Another way might be to have a separate sheet that you can query for the next row that needs updated. 

In sheets, such a formula might look like the following:

The master sheet with data

Then a query sheet that just reference the next row to update using a count function to count how many values are in column B then adding 2 to account for the header row and to give me the next row

 

Userlevel 7
Badge +14

Hi @Acorn_UK 

Try first using a Find Row step in the Zap.

You may also have to add a static value in Column B for the Zap to find and overwrite.

For example, put “FILL” in the Column B cells, then when the Zap runs it will search for  first instance of “FILL” in Column B, and then overwrite the desired column values.

 

Userlevel 1

Hi Troy

I tried your suggestion, the search is working and finding the first cell containing FILL, however the next step is still inserting data after the end of the list.  Do I need to reference the search results somewhere when writing the new data?

https://zapier.com/shared/3eefd686fecc18e1b02807e8947fd3586700521f

Thanks

Userlevel 1

Hi Troy

Is there a way to lookup the first blank cell rather than having to populate the fields that we wish to write to please?

Userlevel 7
Badge +14

@Acorn_UK

Lookup Value is a required field, so I don’t think that approach would work.

 

A more advanced approach would be to use Storage by Zapier and an Formatter step to increment the value to lookup, but that will use more Tasks.

 

Another alternative might be to use Airtable.com instead of Excel.