Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active July 26, 2020 02:01
Show Gist options
  • Save tanaikech/044d8651aed41c886f3379fdf5165aa2 to your computer and use it in GitHub Desktop.
Save tanaikech/044d8651aed41c886f3379fdf5165aa2 to your computer and use it in GitHub Desktop.
Retrieve Last of Specific Row and Column

Retrieve Last of Specific Row and Column

This is a sample script for retrieving the last coordinate of the specific row and column. When the methods of getLastRow() and getLastColumn() of Class Range for Spreadsheet are used, the last coordinates of the vertical and horizontal data range can be retrieved. When users want to retrieve the last coordinates of each row and column, there are no methods. So I created this script. I think that there are several scripts for this situation. So please think of this as one of them. If this was useful for you, I'm glad.

As a sample situation, it supposes the following sheet and the container-bound script. If the formulas are included in the row and column you want to retrieve the last, this script can also check them.

Script

function getLast(range) {
    var getResult = function(range) {
        if (!((range.getNumRows() > 1 && range.getNumColumns() == 1) || (range.getNumRows() == 1 && range.getNumColumns() > 1))) {
            throw new Error("Please input one row or one column.");
        }
        var v = Array.prototype.concat.apply([], range.getValues());
        var f = Array.prototype.concat.apply([], range.getFormulas());
        var i;
        for (i = v.length - 1; i >= 0; i--) {
            if (v[i] != "" || f[i] != "") break;
        }
        return i + 1;
    };
    if (Array.isArray(range)) {
        return range.map(function(e) {
            return getResult(e);
        });
    } else {
        try {
            range.getA1Notation();
        } catch (e) {
            throw new Error("Inputted value is not a range.");
        }
        return getResult(range);
    }
}


// Please run this function
function main() {
    var sheet = SpreadsheetApp.getActiveSheet();

    var range1 = sheet.getRange("A:A");
    var r1 = getLast(range1); // Retrieve last row of column 1.
    Logger.log(r1); // 6

    var range2 = sheet.getRange("1:1");
    var r2 = getLast(range2); // Retrieve last column of row 1.
    Logger.log(r2); // 4

    var range3 = sheet.getRangeList(["A:A", "1:1"]).getRanges();
    var r3 = getLast(range3); // Retrieve both last row of column 1 and last column of row 1.
    Logger.log(r3); // [6.0, 4.0]
}

Please give "range" to getLast(). "range" and "rangelist" can be used for this script as the sample script.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment