Converting Excel VBA Array Function to Google Apps Script for Sheets

[Luke_Brilliant]

I have a VBA function in Excel that creates an array and fills it with a specific value across multiple columns. The function works great in Excel but I need to make it work in Google Sheets using JavaScript instead.

Here’s what my current VBA code does: when I enter =fillCells(8) in cell A1, it puts the number “1” in cells A1 through H1. Basically it creates a horizontal array with the same value repeated.

Function fillCells(count As Long) As Variant
   Dim resultArray As Variant
   Dim index As Long
   ReDim resultArray(count - 1)
   For index = 0 To count - 1
      resultArray(index) = 1
   Next index
   fillCells = resultArray
End Function

I’m trying to figure out how to write the same thing in Google Apps Script so it works in Google Sheets. The goal is to have a custom function that takes a number as input and returns an array that spreads across that many columns, all filled with the same value. Anyone know how to convert this VBA logic to JavaScript for Google Sheets?

[Pete_Magic]

Just return a two-dimensional array from your Google Apps Script function. Google Sheets needs arrays in a specific format for horizontal output.

function fillCells(count) {
  var result = [];
  for (var i = 0; i < count; i++) {
    result.push(1);
  }
  return [result];
}

See those brackets around result? That’s what wraps your array into a row format Google Sheets can recognize. Skip them and it won’t spread horizontally. I learned this the hard way converting my own VBA functions - wasted hours debugging before I figured out it was just the array structure. The loop also gives you more flexibility than fill methods if you need to change the logic later.

[emmat83]

the trick is returning it as a horizontal array. try this:

function fillCells(count) {
  return [Array(count).fill(1)];
}

same as Pete’s solution but more compact. the outer brackets create a 2D array so Google Sheets handles rows/columns correctly. without them, everything dumps into one cell - probably not what you want.

[charlottew]

Here’s the JavaScript equivalent using the Array constructor:

function fillCells(count) {
return new Array(count).fill(1);
}

This creates an array with your specified length and fills every spot with 1. When you call =fillCells(8) in Google Sheets, it’ll spread across 8 columns horizontally - same as your VBA version.

I’ve used this in several projects and it’s way cleaner than VBA’s loop syntax. Google Sheets handles the array output automatically, so no need to mess with Variant type declarations. Just enable the Google Apps Script API if you haven’t already, save your script, and you’re good to go.