Reading values from array with objects from a function in google sheets

Question

I am very new to excel but I have been coding for a couple of years. Now I have created a function which returns an array of objects.

function sheetnames() {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++){
    let totalScore = sheets[i].getRange("C2").getValue();

    if(i == 0 || i == 1){
      continue;
    }
    console.log(sheets[i].getName(), totalScore);
    out.push({name: sheets[i].getName(), score: totalScore});  
  } 
  return out.sort((a)=> a.totalScore);
}

I want to call this function and write Name in A1 and score in B1 and then next row Name in A2 and score in B2 and so on and so on. I'm calling my function in excel using =sheetnames() which i got to work before when I only returned an array with strings.

Thanks!

Daniel Gustafsson · Accepted Answer · 2021-05-26 15:51:06Z

1

I solved it by updating the excelsheet straigt from the code instead of calling the function from the acctual excel.

function sheetnames() {
  resetStandingsSheet();
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++){
    let totalScore = sheets[i].getRange("C2").getValue();

    if(i == 0 || i == 1){
      continue;
    }
    console.log(sheets[i].getName(), totalScore);
    out.push({name: sheets[i].getName(), score: totalScore});  
  } 
  var scoresAndNames = out.sort((a)=> a.totalScore);
  var standingSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  
  standingSheet.appendRow(["Name", "Score"]);
  for(var i = 0; i < scoresAndNames.length; i++){
    standingSheet.appendRow([scoresAndNames[i].name, scoresAndNames[i].score + 'p']);
  }
}

answered May 26, 2021 at 15:51

Daniel Gustafsson

1,8577 gold badges41 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reading values from array with objects from a function in google sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related