Using Google Script to get an array from a list of cells in google sheet

Question

How do I create an array of values that are in a set of cells in Google sheets?

The array should be the same rows and columns as the cells and it should have the same values as the sheet has in each location.

Also, I want to be able to pass the range of the array in as a parameter so that I can use the function for different ranges.

edit 2: New code, nearly working, I just need to have it receive the ranges from user input on the google sheet itself. This is what I am trying to get work, but the beginning is struggling to work, I can't pass in a choice of ranges and have the cell update and have the function run.

Also, I am having a problem with getting a reference error almost every time even when I try to preset the ranges within the function without any parameters

function sortingtest(pWO, pInfo, pSearch) {
    var WO = SpreadsheetApp.getActiveSpreadsheet().getRange(pWO).getValues();
    var Info = SpreadsheetApp.getActiveSpreadsheet().getRange(pInfo).getValues();
    var Search = SpreadsheetApp.getActiveSpreadsheet().getRange(pSearch).getValues();
    //[row][column]

    var FinalArray1 = [];
    var FinalArray2 = [];
    var FinalArray3 = [];
    var LastArray = [];
    var a = 0;
    var b = 0;
    var c = 0;
    var d = 0;

    for (var row = 0; row < WO.length; row ++) {
        var counter = row - 1;
        while (WO[row] == "") {
            WO[row] = WO[counter];
            counter--;
        }
    }

    for (var col = 0; col < Info[0].length; col++) {
        for (var row = 0; row < Info.length; row++) {
            if (Info[row][col] == Search[col]) {
                if (col == 0) {
                    FinalArray1[a] = WO[row];
                    a++;
                }
                else if (col == 1) {
                    FinalArray2[b] = WO[row];
                    b++;
                }
                else if (col == 2) {
                    FinalArray3[c] = WO[row];
                    c++;
                }
            }
        }
    }

    for (var i = 0; i < FinalArray1.length; i++) {
        for (var j = 0; j < FinalArray2.length; j++) {
            for (var k = 0; k < FinalArray3.length; k++) {
                if (FinalArray3[k] == FinalArray2[j] && FinalArray2[j] == FinalArray1[i]) {
                    LastArray[d] = FinalArray1[i];
                    d++;
                }
            }
        }
    }
    return LastArray;
}

This is what my entire function looks like above, but I need it so I can said in a range that I choose to send in and I need the function to update the cell whenever something in that cell range is changed — Michaels
You mention "send (?) in a range", and "input from google sheets"... are you calling this as a custom function from Sheets? That's a Very Important piece of information, and should be clearly stated in the question if it is the case. What does the function call look like? Are the parameters being passed as strings, or do you expect to have something like =sortingtest(A1:C12,D1:E12,F1:G4)? — Mogsdad
Yes I am calling this function from google sheets, sorry about not making that clear, and I do expect to have something like that, but it would also include separate sheets like so: =sortingtest(sheet1!A1:C12,sheet3!D1:E12,sheet2!F1:G4) — Michaels

Jean-Rene Bouvier Jean-Rene Bouvier · Accepted Answer · 2015-06-18T14:15:12

If you call your function from within the spreadsheet as you indicate it in your comment (=sortingtest(sheet1!A1:C12,sheet3!D1:E12,sheet2!F1:G4)), you do not need to call any of the SpreadsheetApp functions to get arrays: pWO, pInfo and pSearch will already be 2 dimensional arrays.

Quoting the Google custom function article:

If you call your function with a reference to a range of cells as an argument (like =DOUBLE(A1:B10)), the argument will be a two-dimensional array of the cells' values. For example, in the screenshot below, the arguments in =DOUBLE(A1:B2) are interpreted by Apps Script as double([[1,3],[2,4]]).

Using Google Script to get an array from a list of cells in google sheet

1 Answers