About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

  • Home
  • Get Help
  • How Do I
  • Enter data to a specific cell in Google Sheet based on both column and row lookup
Best answer

Enter data to a specific cell in Google Sheet based on both column and row lookup

  • 3 February 2021
  • 8 replies
  • 731 views
A
Userlevel 1
Badge

Hi all

I have a Google Sheet set up as a weekly scorecard. 

I have rows with metrics and a column for each week. 
Each week I want to update each metric (row) in the current week column. 

I can see the ability to lookup the row, but it appears the only option is to enter a fixed reference for the column. This doesn’t work as I need it to update to a new column each week. 

Does anybody have any suggestions or workaround to get this to work?!

Many thanks

Adam

icon

Best answer by Troy Tessalone 4 February 2021, 17:48

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.

8 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @adamstanley 

Can you provide screenshots of how your GSheet is configured that you are trying to use in the Lookup step of the Zap?

Can you outline a specific example and the desired output of the Zap?

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
A
Userlevel 1
Badge

Hi @Troy Tessalone ,

 

Thanks for getting in touch. Please see GSheet screenshot here:

 

Essentially, the I have a measurable on each row, and each week I want to enter the result in the current week commencing column.

 

Set up of Zap:

Every Sunday, run the task (fine)

Look up values from another sheet (fine)

 

I think need to find the correct cell reference and find a way to update the value which is where I’m struggling. For example, for Measurable 1 the cell to enter the data would be H4.

 

I can see there is a way to lookup by Row and Updated Row, but can’t find a way to lookup and update a specific cell based on row AND column lookup. 

 

Any help or suggestions much appreciated!

 

Thanks

Adam

 

 

Troy Tessalone
Userlevel 7
Badge +14

Hi @adamstanley 

You may want to consider structuring your GSheet differently, then either employing pivot tables or formulas to get the data to update as you desire, since doing what you’ve described of dynamically populating a specific cell in a row/column isn’t best served for being done via a Zap.

 

Check out this help article about how GSheets need to be structured to be used in Zaps:

https://zapier.com/help/doc/how-to-set-up-your-google-spreadsheet-to-work-with-zapier

  • The first row must have titles for any column headers you want to be able to see in your Zap.
  • The second row must have content for any columns you want to be able to see in your Zap.
  • There can be no completely blank rows before the end of your spreadsheet.
⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
A
Userlevel 1
Badge

Thanks Troy, appreciate it.

The difficulty with a Pivot or formulas is I’m bring in data from a variety of sheets, which all updates dynamically which is why I want to create a ‘timestamp’ each week.

Thanks for the link above - are you aware of any way to target a specific cell to update?

Thanks

Adam

 

Troy Tessalone
Userlevel 7
Badge +14

@adamstanley 

Zaps with GSheets is meant to be done on a row by row basis essentially in a flat file as described in the link.

Zaps with GSheets do not work well when trying to use in a dynamic reporting format.

Best advice I can give is to somehow get the source data into a flat file format (standardized columns), then use pivots or formulas to manipulate the data set into the desired output.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
A
Userlevel 1
Badge

Thanks Troy!

GetUWired
Userlevel 7
Badge +12

Hi @adamstanley 


If you are still struggling with this, I believe I can help you build a custom integration with Google Sheets that will work for you! 

Please send me a direct message if you’re interested! 

A
Userlevel 1
Badge

In the end, I ended up creating a Google script to achieve the desired result rather than using Zapier.

Terms & Conditions