CODEWITHSUNDEEP
google-apps-script
DKappy
DKappy

Reputation: 3

Issue Looping through Google Spreadsheet and Retrieving Cell Values

My apologies in advance for any stupid questions... I'm a completely new to JS and scripting in Google Docs.

I'm trying to write a bit of code to delete all the rows in a google spreadsheet that has the word "Received" or "Canceled" in the first column of the row.

Here is my current code:

function DeleteClosedRows() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var end = SpreadsheetApp.getActiveSheet().getLastRow();
    for( var i = 3; i < end + 1; ++i ) {
        var value =  ss.getActiveRange().getCell(i, 1).getValue();
        if (value === "Received" && value === "Canceled") {
            sheet.deleteRows(i); 
        };
    };  
};

When I try to run this I get the error "Cell reference out of range"

When I look at the Execution Transcript it says the last action taken was: Range.getCell([3, 1])

.. I'm sure there are 100 things wrong with my code, but for now I'd love to understand the first issue that's stoping it from getting past that part.

Thanks for your help!

Upvotes: 0

Views: 3583

Answers (1)

Phil Bozak
Phil Bozak

Reputation: 2822

The reason that your code is failing is because .getActiveRange() returns the range of cells that are currently selected by the user. This is not what you want. Instead, you will want to get a range from the sheet that consists of a single cell. Something like this:

var value =  ss.getRange(i, 1).getValue();

.getRange with two arguments functions exactly like you expect .getCell.

Note: When you call .getRange, you can get a whole range of cells (just like you can select multiple cells in the spreadsheet. Then when you call .getValue, you get the value of the first cell in that range.

Note for performance: You can retrieve the values of a whole range of cells at once by calling .getValues. This returns a 2D array containing all of the values at once. This is much faster than getting the value of one cell at a time.

A couple of linked answers which also explain this point: Link 1 Link 2

Upvotes: 1

Related Questions