Optimal way to set cell values in Google Sheet via Script

Question

I have a template sheet with checkboxes and I want to copy the checked ones to a new sheet. I have a working version that involves adding rows but I am looking for something faster. I thought getting a range of values on both the new and old sheets and working on the arrays would be best but I hit a error:

'Cannot covert Array to Object[][]".

I think the issue has to do with the fact that this is a new unpopulated sheet. The code below is the simplest example of what is happening. Am I doing something wrong, or is this just not possible?

function test(){
  var s = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  var r = s.getRange(1,1,5); 
  var v = r.getValues(); 

  for ( var i=0; i < 5; i++) {
    v[i] = i; 
  }
  r.setValues(v);   //ERROR: Cannot covert Array to Object[][]`enter code here`
}

It looks like the line v[i] = i; converts the Object[][] to an array. So , i think (bizarre) I need to create a new array[][] asfollows:

function test(){
  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var r = s.getRange(1,1,5,1); 
  var v = r.getValues(); 
  var ta = []; 
  for ( var i=0; i < 5; i++) {
  ta[i] = [];
    ta[i].push(i) ;
  }  
  r.setValues(ta); 
}

Steve Levi Steve Levi · Accepted Answer · 2018-09-04T03:20:19

Ok. Here is the full solution. The function looks for the sheet "Work" that has 2 columns; the first is a checkbox, the second is the string value of interest. For every checked box (value == true), the 2nd column's value, Font weight, and Font size are copied into appropriately 'shaped' structures.

Once constructed, a new sheet is created, a range in the new sheet is retrieved and used to set the values, weights and sizes of a single column.

function copyCheckedItems () {
  var cl = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Work');
  if (cl) {
    var cnt      = cl.getLastRow(); 
    var range    = cl.getRange(1,1, cnt, 2 );
    var values   = range.getValues();
    var weights  = range.getFontWeights(); 
    var sizes    = range.getFontSizes(); 

    // Compute data needed for new sheet in the right shape. 
    var tv = []; 
    var tw = []; 
    var ts = []; 
    var newCnt = 0;

    for (var row in values) {
      if(values[row][0]){
        tv[newCnt] = []; 
        ts[newCnt] = [];       
        tw[newCnt] = [];    

        tv[newCnt].push(values[row][1]);
        tw[newCnt].push(weights[row][1]);
        ts[newCnt].push(sizes[row][1]);
        newCnt++; 
       }    
    } 

    // construct the new sheet in a minimum of calls 

    var name     = Browser.inputBox('Enter WorkSteet name');; 
    var sheetOut = SpreadsheetApp.getActiveSpreadsheet().insertSheet(name);      

    var ro = sheetOut.getRange(1,1,newCnt,1); 
    ro.setValues(tv);
    ro.setFontSizes(ts); 
    ro.setFontWeights(tw); 
    //Browser.msgBox("Done.");
  }
  else {
    Browser.msgBox('Work sheet not found!');
  }
}

Optimal way to set cell values in Google Sheet via Script

1 Answers