How to write an array to a range column in Google Sheets Script Editor?

Question

I got a variable that displays this in the logs:

[20-06-04 08:58:27:816 BRT] 2.0
[20-06-04 08:58:27:818 BRT] 6.0
[20-06-04 08:58:27:820 BRT] 14.0

How can I set this to a column range?

I tried setValues and I couldn't get any value in the range.

If I put setValue the range only sets 14.

Anyone can help me?

edit: this is the code that I'm using this variable.

function reconhecerid(){

var app1 = SpreadsheetApp;
var ss1 = app1.getActiveSpreadsheet();
var menu = ss1.getSheetByName("%");;
var regis = ss1.getSheetByName("Crédito");
var data = regis.getDataRange().getValues();

for(var i = 0; i<data.length;i++){
if(data[i][3] == "P"){ //[1] because column B
var hue = ((i+1)-2);

Logger.log(hue);

menu.getRange("A7:A").setValue(hue);

Please post your code in the question. setValue() accepts one value and setValues() accepts a 2D array which looks like [[1,2,3],[4,5,6],[7,8,9]] This is 3 rows and 3 columns the first row is [1,2,3]. The first column is[[1],[4],[7]] — Cooper
– Cooper, Commented Jun 4, 2020 at 12:28
You should get use to making your ranges getRange(7,1,menu.getLastRow()-6,1).setValue() . If there is a P in column 4 in the first iteration then column A will be set to -1 — Cooper
– Cooper, Commented Jun 4, 2020 at 12:43
I still can't make all three values be set. It only shows 14. — Guilherme Lopes
– Guilherme Lopes, Commented Jun 4, 2020 at 12:53
No you misunderstand your writing the same value into the entire column every time you go through the loop — Cooper
– Cooper, Commented Jun 4, 2020 at 12:55

Cooper · Accepted Answer · 2020-06-04 13:49:56Z

2

function reconhecerid(){
  const ss=SpreadsheetApp.getActive();
  const menu=ss.getSheetByName("%");
  const regis=ss.getSheetByName("Crédito");
  let data=regis.getDataRange().getValues();
  let hue=[];
  data.forEach(function(r,i){
    if(r[3]=="P"){ 
      hue.push([(i+1)-2]);
      Logger.log(hue);
    }
  });
  menu.getRange(7,1,hue.length,1).setValues(hue);
}

So your output should look like this in the debugger [[2],[6],[14]]

answered Jun 4, 2020 at 13:49

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

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

1 Comment

Guilherme Lopes Over a year ago

It worked like a charm, thank you so much for your help! Have a great day.

Collectives™ on Stack Overflow

How to write an array to a range column in Google Sheets Script Editor?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related