Skip to content

Instantly share code, notes, and snippets.

@JRJurman

JRJurman/googlesheets_column_builder.js

Created May 27, 2020 02:29
Show Gist options
  • Save JRJurman/85d1292a31799f871ece4e526f6dd871 to your computer and use it in GitHub Desktop.
Save JRJurman/85d1292a31799f871ece4e526f6dd871 to your computer and use it in GitHub Desktop.
Download ZIP
Build Excel Style Columns
Raw
googlesheets_column_builder.js
// get 1,2,3,4...26,27,28...98,99,100
// ret A,B,C,D... Z,AA,AB...CU,CV,CW
const CHAR = i => String.fromCharCode(i)
const MOD = (i, den) => (i % den)
const sif = (conditional, value_if_true, value_if_false) => conditional ? value_if_true : value_if_false
a = Array(855).fill(0).map((_, i) => i)
b = a.map(num => {
return [num,
sif(num < 702, "", CHAR(MOD((num / 26 / 26) - 1, 26) + 65)) +
sif(num < 26, "", CHAR(MOD((num / 26) - 1, 26) + 65)) +
CHAR(MOD(num, 26) + 65)
]
})
/**
=arrayformula(
if(B2:B < 702, "", CHAR(MOD((B2:B / 26 / 26) - 1, 26) + 65)) &
if(B2:B < 26, "", CHAR(MOD((B2:B / 26) - 1, 26) + 65)) &
CHAR(MOD(B2:B, 26) + 65)
)
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment