How to retrieve data from specific column

Question

In my AppScript code, I've the below code, that returns perfectly columns d and e in a JSON format

fileID = "xxxxxxvxjRD_kjE7gzYE3WAcGdxaQEEQNReY"
sheetName = "Data"
function doGet(e) {
 // Logger.log(e.parameter.method);
   // Open Google Sheet using ID
  var ss = SpreadsheetApp.openById(fileID);
  var sheet = ss.getSheetByName(sheetName);
  // Read all data rows from Google Sheet
  const values = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  // Converts data rows in json format
  const result = JSON.stringify(values.map(([a,b,c,d,e]) => ({SupplierName: d,Brand:e,})));
  // Returns Result
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

The values.map(([a,b,c,d,e]) played important rule in the code above.

Now the numbers of column is increasing, and I need 2 columns, that are z and ad instead of d and e, do I need to include all the columns names in the array used in the map function, to be:

values.map(([a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t.....,ad])

Or there is abetter way to use the 2 indexes only.

score 2 · Accepted Answer · answered May 12 '22 at 11:32

2

In your situation, how about the following method?

Sample script:

// Ref: https://stackoverflow.com/a/21231012
const letterToColumn = letter => {
  let column = 0,
    length = letter.length;
  for (let i = 0; i < length; i++) {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
};

const columnLetters = ["Z", "AD"]; // Column letters you want to retrieve.
const res = values.map(r => columnLetters.map(e => r[letterToColumn(e) - 1]));
console.log(res)

In this sample script, in order to retrieve the values from the specific columns, the values are retrieved by converting the column letter to the column number. By this, you can retrieve the values by giving const columnLetters = ["Z", "AD"].

answered May 12 '22 at 11:32

Tanaike

181,128
11
97
165

It worked perfectly, but I did not understand the `length = letter.length;` line – Hasan A Yousef May 12 '22 at 11:51
1

@Hasan A Yousef Thank you for replying. About `length = letter.length`, this is used in the function `letterToColumn`. It seems that here, the length of the column letter is checked. For example, `AD` is 2. Using this, the column number is retrieved in the loop. This function is from [this thread](https://stackoverflow.com/a/21231012). – Tanaike May 12 '22 at 11:56
1

Thanks a lot, I was thinking what could be the meaning of letter length, it looks it is the number of letters in each column name, from "A" to "Z" it is 1, from "AA" to "ZZ" it is 2, from "AAA" to "ZZZ" it is 3, and so on. – Hasan A Yousef May 12 '22 at 12:01
@Hasan A Yousef Thank you for replying. Yes. I think that your understanding is correct. – Tanaike May 12 '22 at 12:03

score 0 · Answer 2 · answered May 12 '22 at 13:52

Maybe you can try get values by columns in this way: Using Advance Sheets Services,majorDimension: "COLUMNS", You can use get or batchGet method. Both works.

  const result ={}
  const values = Sheets.Spreadsheets.Values.batchGet('xxxxxxvxjRD_kjE7gzYE3WAcGdxaQEEQNReY', {
      ranges: [
        //provide the range your need eg from a to z
        "Data!A:Z",
      ],
      majorDimension: "COLUMNS",
    }).valueRanges;

  for ( i in values[0].values){
    //result[header[i]] = values[0].values[i]
     const header = values[0].values[i][0];
     result[header] = values[0].values[i]
  }

  console.log(result)
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);

How to retrieve data from specific column

2 Answers2

Sample script: