Home How to generate the last letter of a google sheet column given the first letter and an array of data?

Questions

How to generate the last letter of a google sheet column given the first letter and an array of data?

May 12, 2022

Now my approach is to generate only one letter, but if there is a lot of data and it comes after column Z, my code breaks.

Working code that will give a, d:

const countData = [1, 2, 3, 4].length;
const initialLetter = 'A'.toLowerCase();
const initialLetterNumber = initialLetter.charCodeAt(0) - 97;
const lastLetter = String.fromCharCode(
  initialLetterNumber + countData - 1 + 97
);

console.log(initialLetter, lastLetter);

Non-working code, in this case I want to get z, ac.

const countData = [1, 2, 3, 4].length;
const initialLetter = 'Z'.toLowerCase();
const initialLetterNumber = initialLetter.charCodeAt(0) - 97;
const lastLetter = String.fromCharCode(
  initialLetterNumber + countData - 1 + 97
);

console.log(initialLetter, lastLetter);

Some inputs and wanted result:

input: a, data length: 1 output: a,
input: a, data length: 2 output: b,
input: a, data length: 3 output: c,
input: a, data length: 4 output: d,
input: a, data length: 5 output: e,

input: z, data length: 1 output: z,
input: z, data length: 2 output: aa,
input: z, data length: 3 output: ab,
input: z, data length: 4 output: ac,
input: z, data length: 5 output: ad,

input: ad, data length: 1 output: ad,
input: ad, data length: 2 output: ae,
input: ad, data length: 3 output: af,
input: ad, data length: 4 output: ag,
input: ad, data length: 5 output: ah,

input: az, data length: 1 output: az,
input: az, data length: 2 output: ba,
input: az, data length: 3 output: bb,
input: az, data length: 4 output: bc,
input: az, data length: 5 output: bd,

input: bz, data length: 1 output: bz,
input: bz, data length: 2 output: ca,
input: bz, data length: 3 output: cb,
input: bz, data length: 4 output: cc,
input: bz, data length: 5 output: cd,

...

I would appreciate any help, regards and have a nice day!

>Solution :

You could take two functions to get an integer value from a string and a string from an integer value.

With this functions, you could adjust the numerical value and get a encodes string of this value.

const
    decode = string => [...string].reduce((r, c) => r * 26 + parseInt(c, 36) - 9, 0) - 1,
    encode = integer => {
        let result = '';
        do {
            result = (integer % 26 + 10).toString(36) + result;
            integer = Math.floor(integer / 26) - 1;
        } while (integer >= 0)
        return result;
    };

for (let i = 0; i < 26 * 27 + 1; i++) {
    const s = encode(i);
    document.getElementById('out').innerHTML += [i, s, decode(s)].join(' ') + '\n';
}