Storing an Array in Google App Script and Printing an Array to Google Sheets Column

Question

I'm working on a Google Sheets and I'm trying to store an column of integers in an array, then clear a column on the sheet and then print the array of integers to a column on the sheet. Please see below for code...

function myFunction1() {

//Declaring the Active Sheet
var mySheet = SpreadsheetApp.getActiveSheet();

//Declaring the range that will make up the Array
var myRange = mySheet.getRange("E10:E328");

//Declaring Array 
var myArray = [[myRange.getValues()]];

//Clearing a range on the Google Sheet
mySheet.getRange('A10:A328').clearContent();

//Printing Array to Google Sheet
mySheet.getRange(15, 5).setValues([myArray]);

}

The above code runs without any errors but does not print my array to the sheet. I've been working on this for a while and have used the following articles to try to fix it...

https://productforums.google.com/forum/#!topic/docs/t99laH0rFc0

Incorrect Range Height - Google Script

Writing 1D array into a sheet column in Apps Script

https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluesvalues

Common errors that i have had when writing the code up to this point is, "Incorrect Range Height" and "Cannot convert Array to Object[][]".

How would i fix this so that my Array prints to the column on the sheet?

Thanks for your time! Any help on this would be great!!

Tom Woodward · Accepted Answer · 2016-10-24 01:19:02Z

1

You're making it a bit more complex than you have to.

function myFunction1() {

//Declaring the Active Sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();  
var mySheet = ss.getActiveSheet();

//Declaring the range that will make up the Array
var myArray = mySheet.getRange("E10:E328").getValues();//already in an array

//Clearing a range on the Google Sheet
mySheet.getRange('A10:A328').clearContent();

//Printing Array to Google Sheet
mySheet.getRange(15, 5).setValues(myArray); //already in an array don't need brackets

}

answered Oct 24, 2016 at 1:19

Tom Woodward

1,72314 silver badges23 bronze badges

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

1 Comment

Vytautas Over a year ago

Have to agree with Tom here var myArray = [[myRange.getValues()]] will return a [[[[a,b,c]]]] (as var myArray = myRange.getValues() returns a [[a,b,c]]) and mySheet.getRange(15, 5).setValues([myArray]) wants to write [[[a,b,c]]] in there which makes no sense

Bee-Jay · Accepted Answer · 2019-11-28 18:42:53Z

// Write data in cell G1, H1, I1

function arrayPlay() {
  
  var newArray = [];

  // Variable that saves the data from G1->I1.
  var cellIn1 = ("G1");
  var cellIn2 = ("H1");
  var cellIn3 = ("I1");
  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Defines where the data is recieved. (G1, H1, I1)
  var cellOut1 = sheet.getRange(cellIn1);
  var cellOut2 = sheet.getRange(cellIn2);
  var cellOut3 = sheet.getRange(cellIn3);
  
  // Recieve the data from those cells
  var data1 = cellOut1.getValue();
  var data2 = cellOut2.getValue();
  var data3 = cellOut3.getValue();
  
  // Puts the data in the Array. (newArray)
  newArray.push(data1, data2, data3)
    
  // Presents the data in Cell 1-3 (A1, A2, A3)) Downwards
   sheet.appendRow([newArray[0]]);
   sheet.appendRow([newArray[1]]);
   sheet.appendRow([newArray[2]]);
  
  // Presents the data in Cell 1-3 (A1, B1, C1) Sideways
  sheet.appendRow([newArray[0], newArray[1], newArray[2]]);

  Logger.log(newArray);
  
}

Ed Nelson · Accepted Answer · 2016-10-24 01:38:01Z

0

It is hard to tell exactly what you are trying to do. But if you are trying to replace column A with column E, just do this:

function myFunction1() {
var mySheet = SpreadsheetApp.getActiveSheet();
var myRange = mySheet.getRange("E10:E328").getValues();
mySheet.getRange('A10:A328').setValues(myRange);
}

answered Oct 24, 2016 at 1:38

Ed Nelson

10.4k2 gold badges32 silver badges29 bronze badges

Collectives™ on Stack Overflow

Storing an Array in Google App Script and Printing an Array to Google Sheets Column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related