How can I make a custom function in Google Sheets return values horizontally?

Other Questions Google Sheets
1

Hey everyone! I’m stuck with a problem in Google Sheets. I made a custom function that gives back an array, but the results are going down the column instead of across the row. Here’s what I’m working with:

function lookupMultipleSheets(first, last, cellRef) {
  let ss = SpreadsheetApp.getActive();
  let sheetList = ss.getSheets();
  let results = [];
  
  for (let i = first; i < last; i++) {
    let currentSheet = sheetList[i];
    let value = currentSheet.getRange(cellRef).getValue();
    results.push(value);
  }
  
  return results;
}

This function looks through several sheets and grabs values from the same cell in each one. But when I use it, the output fills up the column below. How can I make it go sideways instead? I tried checking Google’s docs, but they weren’t much help. Any ideas on how to fix this? Thanks!

2

I’ve dealt with this exact issue before, and there’s actually a simple solution. The key is to wrap your results array in another array before returning it. This tells Google Sheets to treat the output as a row instead of a column. Here’s how you can modify your function:

function lookupMultipleSheets(first, last, cellRef) {
  // Your existing code here
  
  return [results]; // Wrap results in another array
}

By adding those square brackets around ‘results’, you’re essentially creating a 2D array with one row, which Google Sheets will interpret as a horizontal output. I’ve used this trick in several of my spreadsheets and it works like a charm. Give it a try and let me know if you run into any issues!

3

hey mate, i had same prob before. try using TRANSPOSE() function around ur custom func. like =TRANSPOSE(lookupMultipleSheets(…)). it flips the output from vertical to horizontal. super easy fix, no need to change ur code. lemme know if it works for ya!

4

I faced a similar challenge in my work with project tracking spreadsheets. The solution was to adjust the array structure by returning a 2D array—in this case, wrapping the results array inside another array. This tells Google Sheets to interpret the output as a row of values instead of a column. Here’s the modified function:

function lookupMultipleSheets(first, last, cellRef) {
  let ss = SpreadsheetApp.getActive();
  let sheetList = ss.getSheets();
  let results = [];
  
  for (let i = first; i < last; i++) {
    let currentSheet = sheetList[i];
    let value = currentSheet.getRange(cellRef).getValue();
    results.push(value);
  }
  
  return [results]; // Wrap the results in another array
}

This change should result in a horizontal array output. Give it a try and adjust further as needed.