Best answer

Google Sheets - Enter data in column rather than rows

  • 12 October 2022
  • 3 replies
  • 943 views

Userlevel 1

I am trying to create a Google Sheets workflow tracking spreadsheet that will automatically create a new sheet for each customer by copying a template sheet that has task lists and checkboxes and then populate our customer’s information when a form is filled out. Since each sheet will be individual to the customer, spreading the customer data out in one row over many columns doesn’t make sense and would just result in having to scroll far to the right to get the information needed while leaving the rest of the vertical space blank. I want the customer data to be input in one column so that the other columns can be used for the task lists/checkboxes. 

Screenshot of the template formatted the way that I want.

 

I am struggling to determine how to do this, or if it’s even possible. I have tried the “Create spreadsheet row(s) with line item support” function, which works as far as entering the data in one column, however it also pushes all of the task lists down as it inserts rows above them (see below).

 

 

Having to add the data one at a time using the “Update row” function is not a judicious use of resources as it would cause us to use up 15 tasks each time as opposed to just one so I would prefer not to do that. 

Is there a way to accomplish what I am trying to do or is this something I’m going to have to find another tool for?

icon

Best answer by RALaBarge 17 October 2022, 16:26

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.

3 replies

Userlevel 4
Badge +7

Hey there @AshleighDLC !

Interesting use case!  As you have seen Zapier only offers Create/Update Row actions -- we don’t have any options to update columns.  While it looks like it could technically be possible based on looking at the Google Sheets API, us offering Create/Update Column options, I think you might be able to get away with using our Update Row actions.

You said you got the first part of your request done.  Using the Google Drive app would be the way I approached that.  Let me know if you did have any more Qs about this.

The main restriction would be that each Sheet you create with this method in Zapier always writes into the exact same number of cells/rows for each sheet.  It isn’t easy in Zapier to handle a variable amount of actions [0] in a Zap, so my suggestion would be to do something like this for your 2nd Zap:

 

Google Drive Trigger => Google Sheets Update Row action => Google Sheets Update Row action => …

 

Have each update write to whatever columns you wish and add an action for each.  Since you have a template, it might work out that you always only write to 9 (or however many) cells in Column B (for example).  We do not have a way to use a single step to Update multiple Rows, but a Zap like this could work for you. 

 

[0] - We do have a “Looping by Zapier” app that could help with this type of thing but that would require you to create a LIne Item Object to feed it in order to update all of the rows needed.  The main suggestion above is more “straightforward” since it does not deal with composing Line Item Objects.

Userlevel 7
Badge +12

Hi @AshleighDLC!

If I’ve understood your question, you need to create a copy of a sheet each time you have a new customer. To do that, you can use the Create Spreadsheet action. 

When you’re setting up the action you can choose an existing sheet to copy, so you can select your template there.

 

If I’ve misunderstood what you’re looking for, please let me know. Otherwise let us know if this works for you!

Userlevel 1

Hi there, 

I can copy the sheet no problem. As I said, the issue that I’m having is entering the data in one column rather than by rows after the new sheet is created, without having the rest of the information on the sheet pushed down, as is shows in my second screenshot. So, what I want is - 

  1. Copy worksheet, copying the template that has information in columns A & D-I already (I already have this figured out)
  2. Insert customer data into column B, without creating new rows that push the information in columns D-I down. This is the part that I’m having trouble with. 

Thank you