Best answer

Google Sheets Variable Column

Userlevel 1

Hello All,

I’m trying to create a weekly data summary in Sheets using Zapier.  In my rows I have my locations, and in the columns my weekly dates.  I’m able to find the proper row I need rather easily, the issue is the column.  The column will obviously change week to week, but in Zapier, I only seem to have the option to add a value to a predetermined column, not a column that changes based on input.


Is there anyway to do this?  I feel like this is probably a super basic answer that I’m missing, but appreciate any help!


Best answer by ForYourIT 4 May 2020, 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.

2 replies

Userlevel 7
Badge +7

Hi @kstamm,

The Zapier app Google Sheets will use the columns basically as unique ID’s to lookup values. This will probably be hard to lookup a dynamic column unless someone else has ideas.
If possible, you could change the template of your Sheet a bit to integrate it for Zapier. Instead of using the 2nd rows as “columns”, put all info on the top row like this;

Then by using an action “Code by Zapier” in your workflow just before the Google Sheets Lookup action you can create the dynamical dates, and use it in the next Google Sheets lookup action;

The code would look something like this, depends on what you would like to achieve;

var today = new Date();
var dd = String(today.getDate()).padStart(2, '0');
var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!
var yyyy = today.getFullYear();

today = mm + '/' + dd + '/' + yyyy;

output = [{today}];

Hope this helps you out! Let me know if you have any additional questions.



Userlevel 1

Hello Bjorn,

Thanks for the help!  I’m actually doing something similar already, but it requires us to transfer the data to a nicer looking/easily digestible report for higher ups.  I was hoping for an all in one solution here. 


If anyone has any thoughts on being able to change the column on the fly, I’d really appreciate it.  Otherwise might have to stick with a slightly manual entry at the end of every week.