Converting Column Number to Letter for Cell Address in Google Sheets with make.com

How To
1

I am working with a make.com automation that involves the ‘Google Sheets - Watch New Rows’ module, where I monitor new entries in a spreadsheet. From the data output of this module, I need to extract the column and row information to construct a cell address that I can use in a subsequent ‘Google Sheets - Update a Cell’ module.

image
image518×714 41.2 KB

image

In the output from the ‘Watch New Rows’ module, I receive a number, specifically the number ‘11’, which represents the column. However, for the ‘Update a Cell’ module, I require the corresponding column letter (e.g., ‘L’) that represents this column number in the Google Sheets.

I am encountering a challenge converting the raw column number ‘11’ into its equivalent column letter, as the data type and the conversion method are not immediately clear to me within the context of make.com. I need to find a way to map or convert this number to the correct column letter dynamically, as it will be used to specify the cell address for updating a cell in the spreadsheet.

Many thanks,
Mirek

2

Welcome to the Make community!

If your column number is <= 26, you can do this

{{ get(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ"; emptystring); ColumnNumber % 26 + 1) }}

Screenshot_2023-12-30_001211
Screenshot_2023-12-30_001211832×50 6.52 KB

3 Likes
3

Thank you :smiley:

Unfortunately, is not working:

image
image446×650 32.3 KB

image
image1364×406 40.2 KB

(I didn’t set row), but column should be L not A, every single time is only A.

Mirek

4

Hi @Miroslaw_Bagrowski,

I’ve been digging into Google API a lot lately for some recent projects, and as it turns out you could use R1C1 format in many places where a cell is requested, such as in this case.

This should work for you, R5C11
image

4 Likes
5

So how I can change:

image

Into number? I don’t know which type of value is column number from Google Sheets module.

6

If you know which column “Done?” will be in (in this case, it’s 22), then you can use:

R#C22

3 Likes
7

Yes, I can write number, it’s no problem, but I’ll try to do this automatic.