How to retreve data from specific column

May 12, 2022

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.

>Solution :

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"].