How to assign an object from column values in Google Sheet?

user_12472644 0
Viewed 163

I have a google sheet with some data in a single column. How to assign these values to new object like {'value[0]':value[0], 'value[1]':value[1],..., 'value[i]':value[i]}?

I wrote this script, but it assigns pair from last value of names only:

function getIngredientsList() {
  const url = "SPREADSHEET_URL";
  const ss = SpreadsheetApp.openByUrl(url);
  const ws = ss.getSheetByName('Base');
  const names = ws.getRange(2, 3, ws.getLastRow() - 1).getValues().map(a => a[0]);
  let nameList;
  for (let i = 0; i < names.length; i++){
    if (names[i] !== ""){
      let name = {[names[i]]:names[i]};
      nameList = Object.assign(name);
    }    
  } 
  return nameList;
}

Where I'm wrong and how to fix it?

arrow-functions google-apps-script google-sheets javascript
2 Answers

I believe your goal as follows.

  • You want to retrieve the values from the cells "C2:C28", and want to create an object that the key and value are the same.

For this, how about this modification? The arguments of Object.assign() is Object.assign(target, ...sources). So in your script, nameList of let nameList = {} is required to be used like nameList = Object.assign(nameList, name).

From:

let nameList;
for (let i = 0; i < names.length; i++){
  if (names[i] !== ""){
    let name = {[names[i]]:names[i]};
    nameList = Object.assign(name);
  }    
}

To:

let nameList = {};  // Modified
for (let i = 0; i < names.length; i++){
  if (names[i] !== ""){
    let name = {[names[i]]:names[i]};
    nameList = Object.assign(nameList, name);  // Modified
  }
}

Or, as other pattern, when reduce is used, the following script can also return the same value with above modified script.

const nameList = ws.getRange(2, 3, ws.getLastRow() - 1).getValues()
.reduce((o, [e]) => {
  if (e != "") Object.assign(o, {[e]: e});
  return o;
}, {});

Reference:

user_71086530

More cleaner way to do that is using reduce method:

var names = ['ABC', 'XYZ', 'PQR'];
var result = names.reduce((acc, elem)=>{
    acc[elem] = elem;
    return acc;
},{});

console.log(result);

user_100048930
Related