How to get a new table with keys and sum results in Google Spreadsheets?

Question

I got a script to count sum for each column in my Google sheet. I'm getting correct result in a Dialog message. Now I need to make a chart from this data. So I need to extract keys and values and insert to sheet in active spreadsheet.

function countNutrients(){
  var sh=SpreadsheetApp.getActiveSheet();
  var hA=sh.getRange(2,23,1,sh.getLastColumn()-22).getValues()[0];
  var sum={};
  hA.forEach(function(h,i){sum[h]=0;})
  var vA=sh.getRange(3,23,sh.getLastRow()-1,sh.getLastColumn()-22).getValues();  
  vA.forEach(function(r,i){
    r.forEach(function(c,j){
      if(!isNaN(c)) {
        sum[hA[j]]+=c;
       Logger.log(sum);
      }
    });
  }); 
  var html="";
  var keys=Object.keys(sum);
  keys.forEach(function(k,i){
    html+=Utilities.formatString('<br />sum[%s]=%s',k,sum[k]);  
  })
  var ui=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(ui,'The dish contains');
  

}

Any ideas how to get it? Thank you!

Answer 1

Understanding

• You want to retrieve the keys and values from the object of sum as an array.
• You want to put the retrieved values to a sheet in the active Spreadsheet.
• You want to achieve this by modifying your script.

Modification point:

• In this answer, the keys and values are retrieved from the object of sum using Object.entries().

Modified script:

When your script is modified, it becomes as follows. Before you run the script, please set the sheet name that you want to put the values.

From:

var html="";
var keys=Object.keys(sum);
keys.forEach(function(k,i){
  html+=Utilities.formatString('<br />sum[%s]=%s',k,sum[k]);  
})
var ui=HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModelessDialog(ui,'The dish contains');

To:

var values = Object.entries(sum);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);

Note:

• In this case, the keys and values are put to the column "A" and "B", respectively. If you want to put the keys and values to other columns, please tell me.
• When you use this modified script, please enable V8 at the script editor.

Reference:

• Object.entries()