CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheets
Charlie Gorrill
Charlie Gorrill

Reputation: 1

Move 2d Array to Spreadsheet

How do I take a 2d Array in google script and push the whole thing to a range in a spreadsheet?

Here is what I have:

Assume that array is created and filled and has 6000+ rows and 4 columns.

The following code gives me an incorrect range error.

Incorrect range width was 0 but should be 4 (line 56, file "Code"):

formatted.getRange(1,1,targetArray.length,targetArray[0].length).setValues(targetArray);

The following code functions but times out because the data set is too large:

for(var i=0; i<formattedSheetLength; i++)
    formatted.getRange(i+1, 1, 1, 4).setValues([targetArray[i]]);

Upvotes: 0

Views: 2649

Answers (1)

Wicket
Wicket

Reputation: 38150

Edit Notes: Due to an OP comment, the original code was changed.

Short answer

Try 

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  // Create a jagged array for demonstration purposes 
  var targetArray = [
    [1,2],
    [1],
    [1,2,3],
    [1]
  ];

  /**
   * These two loops are safe because they don't make calls to 
   * the SpreadsheetApp service.
   */

  var height = targetArray.length;
  //Check the max width.
  var width = 0; 
  for(var i = 0; i < height; i++){
    width = Math.max(width,targetArray[i].length);
  }
  //Add the required empty values to convert the jagged array to a 2D array
  var temp;
  for(var i = 0; i < height; i++){
    temp = targetArray[i].length;
    for(var j = 0; j < width - temp; j++){
      targetArray[i].push('');
    }
  }
  /**
   * Send values to the spreadsheet in a single call
   */
  var range = sheet.getRange(1,1,height,width).setValues(targetArray);
}

Explanation

According to the error message, targetArray[0].length is returning 0. We could assume that targetArray is a jagged array instead of a 2D array. In order to avoid the use of a hardcoded width, the above code as an example of how to convert a jagged array into a 2D array.

The code has two loops that are safe to use because they doesn't call a Google Apps Script service.

Upvotes: 2

Related Questions