3

Hello: Using Google Spreadsheet API V4 on Java, I want to to get the column letter given the column number. Example: the column letter for column number 2 is B.

Is there some way already provisioned in Google spreadsheet API OR is there any other simple way to go do this OR do i need to write my own conversion?

Bipo K
  • 395
  • 3
  • 20
  • You may also refer with this related [SO post](http://stackoverflow.com/questions/39157055/e). Based from the [documentation](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#findreplacerequest), you can find the value using `FindReplaceRequest()` and `.getFind();`. Also, you can use the [`match()`](https://support.google.com/docs/table/25273?visit_id=1-636264411996744651-4221450974&hl=en&rd=2) which can look up a string in an array as suggested in this [thread](http://stackoverflow.com/a/20010848/5832311). – abielita Mar 30 '17 at 08:21
  • Thanks @abielita. Reviewed the information provided. However, could not resolve the problem i.e. to get the Column Letter given the Column Number. – Bipo K Mar 30 '17 at 12:47

2 Answers2

5

Though the Question still is: Is there a native way in Google Spreadsheet API V4 to get the column letter given the column number?

However, for future reference i am providing below a collation in case someone needs to implement his own conversion.

Case 1: Get Column Letter Given the Column Number

public void ColumnNumberToLetter() {
    int inputColumnNumber = 49;
    String outputColumnName = "";
    int Base = 26;        
    String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";        

    int TempNumber = inputColumnNumber;
    while (TempNumber > 0) {
       int position = TempNumber % Base;
       outputColumnName = (position == 0 ? 'Z' : chars.charAt(position > 0 ? position - 1 : 0)) + outputColumnName;
       TempNumber = (TempNumber - 1) / Base;
    }
    System.out.println("ColumnNumberToLetter :" + inputColumnNumber + " = " +  outputColumnName);
}

Case 2: Get Column Number Given the Column Letter

public void ColumnLetterToNumber() {
    String inputColumnName = "AW";
    int outputColumnNumber = 0;

    if (inputColumnName == null || inputColumnName.length() == 0) {
        throw new IllegalArgumentException("Input is not valid!");
    }

    int i = inputColumnName.length() - 1;
    int t = 0;
    while (i >= 0) {
        char curr = inputColumnName.charAt(i);
        outputColumnNumber = outputColumnNumber + (int) Math.pow(26, t) * (curr - 'A' + 1);
        t++;
        i--;
    }
    System.out.println("ColumnLetterToNumber : " + inputColumnName + " = " +  outputColumnNumber);
}
Bipo K
  • 395
  • 3
  • 20
  • 1
    This was exactly what i was looking for, and it seemed to be working till i tried it for a bigger column number (BG actually) and it didnt work. I think you overlooked something on your calculation and I think the problem is on the pow(). but i cant point it right now. will have a look at it and get back to you – Skaros Ilias Dec 28 '17 at 20:52
  • 1
    figured it out. You need to add `+(int) Math.pow(26, t)` at your calculation.the problem was that you dont calculate the letters properly. you get it right for one letter, and works ok for AA-AZ, but when it gets to the B_ zone you loose 26 columns. the same for AB_. tested this with 3letter column and works fine. nice work – Skaros Ilias Dec 28 '17 at 21:13
2

I think you could also just use the R1C1 notation mentioned here:

https://developers.google.com/sheets/api/guides/concepts

This let's you use column numbers instead of letters.

albrnick
  • 1,151
  • 11
  • 15