CODEWITHSUNDEEP
google-apps-scriptspreadsheet
SoftwareTester
SoftwareTester

Reputation: 1090

How to fill a range in a spreadsheet using GAS

I would like to know how to fill and store a range of several rows and columns in a spreadsheet. I tried :

   var sheet = ss.getSheets()[0];
   var row  = 1;    // Start at row 1
   var col  = 1;    // Start at column 1
   var rows = Count;    // Number of rows to use
   var cols = 10;   // Number of columns to use
   var range = sheet.getRange(row, col, rows, cols);     

   var arrValues = [];
   var arrRows = [];

   for (var i=0; i<Count; i++)
   {
      arrValues[0] = i;
      ....
      arrValues[9] = 'something';

      arrRows[i] = arrValues;
   }

   range.setValues([arrRows]);

but that doesn't work.

How should I fill and set the values ?

Upvotes: 0

Views: 2144

Answers (1)

Serge insas
Serge insas

Reputation: 46802

How about something like this, building a 2D array in 2 loops as you did ...

Not sure about what you wanted to do with numerical index so I put it in col 1

function fillRange(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheets()[0];
  var row  = 1;    // Start at row 1
  var col  = 1;    // Start at column 1
  var Count = 10;
  var rows = Count;    // Number of rows to use
  var cols = 10;   // Number of columns to use
  var dataArray = [];
  for (var i=0; i < rows; i++){
    var rowData = [];
    rowData.push(i);
    for(var c=1 ; c<cols ; c++){
      rowData.push('something');
    }
    dataArray.push(rowData);
  }
  Logger.log(dataArray+' length = '+dataArray.length+'   width = '+dataArray[0].length);
  sheet.getRange(row, col,dataArray.length,dataArray[0].length).setValues(dataArray);
}

If you need to control the data in each column, use an array to store these as a constant and change the code like this :

var someData = ['','value1','other value','another one','again','and','another','data','to be','complete']

function fillRange(){
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheets()[0];
  var row  = 1;    // Start at row 1
  var col  = 1;    // Start at column 1
  var Count = 10;
  var rows = Count;    // Number of rows to use
  var cols = 10;   // Number of columns to use
  var dataArray = [];
  for (var i=0; i < rows; i++){
    var rowData = [];
    rowData.push(i);
    for(var c=1 ; c<cols ; c++){
      rowData.push(someData[c]);
    }
    dataArray.push(rowData);
  }
  Logger.log(dataArray+' length = '+dataArray.length+'   width = '+dataArray[0].length);
  sheet.getRange(row, col,dataArray.length,dataArray[0].length).setValues(dataArray);
}

Upvotes: 1

Related Questions