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

0
votes

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!!

3
javascriptarraysgoogle-apps-scriptgoogle-sheets

3 Answers

1
votes

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

}
1
votes

// 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);
  
}
0
votes

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);
}