How to retrieve data from specific column-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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);