Google Apps Script – Iterating Through Ranges in Sheets the Right and Wrong Way

Google Apps Script: SpreadsheetApp, getRange, getLastRow, getDataRange

I was trying to rush out some Google Apps Script code to deal with a task on Google Sheets recently. Basically, I had to search through a heap of data and find certain values and do something too them.

My column was reaching across the page to something like Column BK and my rows were over 1000 deep. Running this code was taking forever!!!

My immediate instinct was:

What have I done wrong?

…and my instinct was right.

The Good and Bad Way to Search Through Code

So after looking at my code again, I discovered that for some reason I go it into my head that I should be searching each cell for the value I needed and then doing something with it.

Sounds logical right? It’s sorta what you are meant to do.

The problem is that I was calling the sever and asking for the range in each cell as I was looping through the entire document. This is super costly and inefficient in terms of time.

Google talks about this in their Google Apps Script Best Practice page under Batch Operations.

Also, if you do run a costly code like this, then you will get a little red light in your Script tool bar that represents your Execution Hints:

Google Apps Script Execution Hints

Clicking on Execution Hints and expanding the side bar with provide you with a far-too-deserving-polite dressing down about your slow and server costly code.

Method Range.getValue is heavily used

The Good

So instead of calling the server for each cell, I need to get the full range of the data I am working on, search through it client-side to find what cells require modifying and then invoke the modifications.

Let’s look at a simple example:

Example

In this example, I want to search through all the results over multiple quizzes and if there is a dash “-” or a zero “0” change the background accordingly.

Yeah! Yeah! I can do this with conditional formatting, but this is an example, damn it, Jim!!!

Below is a sample of the data I will use and here is the link.

Both Good and Bad examples have the same end result. The result should look like below:

?You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! ?

First let’s set up the Google Apps Script code file by calling the user interface, sheet, and range of data. (Lost? Create your first Apps Script)

Hopefully, everything is self-explanatory here. We call the spreadsheet first and then look for the active sheet. Inside the active sheet, we want the range of the data (rangeData) which will contain all the data in the range. We will use that data to get the last row and column number of the data. Finally, we will call the server to get the range we want to work with (searchRange).

Once done, we will create our function, onOpen(). When it is called it will create a menu called Checker with the submenu Bad Way and Good Way. This isn’t necessary, but it might be easier for you to physically test the difference in the slow method versus the fast (correct) one.

The Bad Way

As mentioned above in the Bad (slow) Way we call the server each time to look at what is in a cell.

As I loop across the columns and then the rows, I am using my search range to get the value of the cell in Line 25. This means I am contacting the server a total of 436 times. This significantly slows things down.

The Good Way

 

In the preferred approach, I am taking the array that I created from searchRange.getValues() in Line 36  and searching through it before I make my calls to change the background when a dash or a zero occurs.

Why is this better?

I only make a server call to collect the range data once. Then client-side (on my computer in this instance), I do all my searching before calling Google who collects all the changes in a cache until the loops are done before creating background colours all at once.

Super fast.

Speed comparison. 

Take a look at the speed differences over ten tests:

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

The Good Way is the clear winner. You can try it out for yourself if you have been playing along by going to <View><Execution Transcript> in the Script

Hire a Google Workspace Developer for your Business Needs

Take-Home

The take-home from this is that, make as little calls to the server as possible. It significantly improves your speed.

The Full Code

 

Update June 2019: In the above tutorial I looked at how to iterate through a spreadsheet efficiently, however, I didn’t look at how to return data back to the Google Sheet in a very efficient fashion, for example when you want to delete rows based on cell values or update formatting. Check out the post below to guide you on this:

Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


27 thoughts on “Google Apps Script – Iterating Through Ranges in Sheets the Right and Wrong Way”

  1. How could I code to compare diferent version of a Spreadsheet and compare with the last version and set the backgroundcolor for the changed cells?
    maybe with arrasýs and compare each iterator?

    Reply

  2. lease help me with a tip. I need to copy and paste non-contiguous cells from one sheet to another. What I am doing in very slow. How can I improve the time?
    My code:
    for (i = 0; i <matinfo [1] .length; i) {
    valdest = sheetConsol.getRange (matinfo [1] [i]). getValue ();
    sheet.getRange (matinfo [2] [i] row) .setValue (valdest);
    }

    example of matinfo [] []:

    mapsps [0] = [2]; // Number of sheet with the consolidated
    mapsps [1] = [“T31”, “T32”, “T33”, “T34”, “Z35”, “Z36”, “Z37”, “K23”, “K25”, “N23”, // Self-evaluation
    “AF31”, “AF32”, “AF33”, “AF34”, “AL35”, “AL36”, “AL37”, “T23”, “T25”, “W23”, // Evaluation
    “U18”, // Total Score
    “B63”, “AD63”, “B69”, “AH69”, “B70”, “AH70”, “B71”, “AH71”, “B72”, “AH72”, “B73”, “AH73” // Comments
    ]; //Origin

    Reply

    1. Hi Carlos,

      It looks like you have different blocks of columns and rows that you want to select. Try using getRangeList() it takes A1 or R1C1 notation. Then use getRanges() and loop through the ranges to getValues() and push it into an array.

      ~Yagi

      Reply

  3. So I’m trying to loop through about 340k rows and delete any rows that contain any of the words in an array in column C… And it’s taking F.O.R.E.V.E.R.

    Like, it’s been running for at least 45min now. Weird thing I can’t figure out though, is that I’m working from the bottom up, but I don’t see any values changing when I scroll to the bottom of the sheet. All I see changing are the row/line numbers.

    Here’s my script… Did I do it the bad way?

    function deleteRows() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName('Sheet1');
    var r = s.getRange('C2:C');
    var v = r.getValues();
    var a = ["_qty","_tax_class","_product_id","_variation_id","_line_subtotal","_line_total","_line_subtotal_tax","_line_tax","_line_tax_data","state","loan-type","Loan Type","number-of-units"];
    var b = [];
    for(var i=0; i<v.length; i++) {
    a.forEach(function(w) {
    if(v[i].toLowerCase() == w.toLowerCase()) {
    b.push(i+2);
    }
    });
    }
    for (var i = b.length - 1; i>=0; i--) {
    s.deleteRow(b[i]);
    }
    };

    Reply

    1. Hi Josh,

      for your script to work you just need to change this:

      However, as you have discovered, it is still going to take forever.

      You have inspired me to create a post and link it to this one:
      Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet

      Hopefully this gives you a better idea on how to delete rows with big data sets.

      Having said that, with 340K rows it might be time to consider pushing this sheet into a database, using something like Firebase for something more responsive in the future.

      I hope this helps.

      ~Yagi

      Reply

  4. Hi,

    I need to loop through a spreadsheet & check a single column for a yes or no response & then automate an email based on the response (using an if else statement). How can i loop through a single column?

    BW – T

    Reply

    1. Hi Tate,

      Have a look at line 7 of the code:

      The getRange method takes 4 arguments:
      1. Start Row
      2. Start Column
      3. Number of Rows
      4. Number of Columns

      This will define the range of your selected items.

      Say if you want just column B starting from row 2 (Just below a header). You might do something like this:

      Here are some tutorials about using Google Sheets to send emails based on a column value:
      Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click
      Google Apps Script: Create an HTML email reminder service from Google Sheet data.

      ~Yagi

      Reply

  5. Hi Yagi, i’ve been following your posts regarding loops and how to add automatic dates from your web. I have one questions that i’ve been stuck forever. If you could give me a hint regarding how to do this (explained below), it would be very helpful.

    So, basically, i’m trying to send an email based on numbers on several cells, tried to learn loops with no avail. Currently my code looks like this.

    var ClassMeetingRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Level 1”).getRange(“B4”);
    var ClassMeeting = ClassMeetingRange.getValue();

    // Fetch the email address

    }
    }
    }

    With this google script, i can only received an email if “B4” showed number 8 in its cell, but not when other cell (also column B) showed number 8. However, i would like to received email if, say range B4:B10 have number 8 in any of its cell.

    Hope you understand my bad english. And for your info, i have no coding background whatsoever.

    Thank you

    Reply

    1. Hi Eric,

      I think the best thing for you to do would be to collect the entire range with your numRows. Two seem awfully small. You will also need to add an if statement inside your for loop, say:
      if(row[0] == 8){put your email code here}

      Hope this helps.

      Reply

  6. Hi Yagi,

    Great blog, I’ve been learning a lot from here.

    I have Google sheet where I’ve imported data from an email (using Google scripts) and I need to combine every three rows of text into one row. e.g. =JOIN(” “,B10:B12) put result in C10. The next would be =JOIN(” “,B13:B15), put result in C11, etc… for however much data is in the sheet. Unfortunately I can’t think of a way to automate the JOIN function for every 3rd row. Is it possible to create a script to do this? I can manually do the JOIN function in Sheets, but it is very time consuming.

    Kind regards,

    Mike

    Reply

    1. Hi Mike,

      There is a join method in Javascript that will do just that.

      Once you get the entire range of your sheet onto Google Apps Script, you could use something like map or a for loop to iterate through each row and update column C.

      ~Yagi

      Reply

  7. Hello Yagi,

    First, thanks for the post/blog! I’ve been learning a lot from you!

    I’ve been trying to modify your script, particularly the “setBackground” color part to where it will hide the column based on a condition. In the following example, I have a named range for header row that will change, say, if a row is inserted. I want to

    (1) select this range
    (2) get values from the range similar to the following line in your code: “var rangeValues = searchRange.getValues();”

    (3) loop through the values (for the defined range)
    (4) check if there’s a value or if the value is blank (“”)
    (5) hide the column of the cell if the cell value is “”
    (6) show the column of the cell if the cell contains a value (any value)

    Here’s what I have so far:

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var hrRange = sheet.getRange(‘headerRow’);
    var hrIndex = hrRange.getRow();
    var hrFirstColumn = hrRange.getColumn();
    var hrLastColumn = hrRange.getLastColumn();
    var hrArray = hrRange.getValues();

    function hideColumns() {
    // Get array of values in hrArray
    var rangeValues = hrArray;

    for ( i = 0; i < hrLastColumn; i++){
    if(rangeValues[i] === “”){
    var columnIndexRange = sheet.getRange(hrIndex,i+1);
    var columnIndex = columnIndexRange.getColumn();
    sheet.hideColumns(columnIndex);
    }else if (rangeValues[i] != “”){
    var showColumnIndexRange = sheet.getRange(hrIndex,i+1);
    var showColumnIndex = showColumnIndexRange.getColumn();
    sheet.showColumns(showColumnIndex);
    };
    };
    };

    As you can tell from the script, it’s a mess. Don’t waste your time, but any help is appreciated!

    Sincerely,
    Thom

    Reply

    1. Hi Thom,

      I am not sure what you are trying to achieve here: var hrRange = sheet.getRange(‘headerRow’); Is this a placemarker for your actual range, say hrRange = sheet.getRange(1,1,1,20); or hrRange = sheet.getRange(1"A1:K1"); ?

      Are you planning on running this based on a user action or when a button is checked?

      Cheers,

      Yagi

      Reply

      1. Hello Yagi,

        I’ll try and explain the best I can. I’m very new to all of this, but here’s more or less what I’m trying to do:

        I’m trying to select a range of cells. These cells will be from a named range (in the following code, the named range is ‘headerRow’, and consists of cells B2:G2). I want to be able to expand this range, move it, or shrink it, so I want to be able to make it B2:J2 (expand it), C9:H9 (move it), or B2:F2 (shrink it), respectively, all while the script will still locate the range and work. The range will always consist of a single row.
        Once this range is selected, I want to get the values from this range of cells.
        Once I get the values, I want to loop through these values, and then (a) hide the column if the cell value is blank or (b) show the column if the cell value is not blank.

        I’ve nearly gotten it to work in the following, but I get an error (“TypeError: Cannot read property ‘0’ of undefined (line 41, file “macros”) if I move the range to C9:H9, keeping with the example. Yet if I move the same range back to B2:G2, then it works without an error. Line 42, btw, is this line: if(rangeValues[j][i] === “”){

        Here’s what I got so far (and I’ll remove the log function and the coloring once I get it to work):

        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        var hrRange = sheet.getRange(‘headerRow’);
        var hrIndex = hrRange.getRow();
        var hrFirstColumn = hrRange.getColumn();
        var hrLastColumn = hrRange.getLastColumn();
        var hrArray = hrRange.getValues();
        var i;
        var j;

        function log(){
        Logger.log(hrRange)
        Logger.log(hrIndex)
        Logger.log(hrFirstColumn)
        Logger.log(hrLastColumn)
        Logger.log(hrArray)
        Logger.log(hrArray[1])
        Logger.log(i)
        Logger.log(j)
        };

        var ui = SpreadsheetApp.getUi();

        function onOpen() {
        ui.createMenu(‘Sync CRE Work’)
        .addItem(‘Sync Spreadhseet with Master’, ‘hideColumns’)
        .addToUi();

        hideColumns()
        };

        function hideColumns() {
        // Get array of values in the search Range
        var rangeValues = hrRange.getValues();

        // Loop through array and if condition met, add relevant
        // hide or unhide column.
        for ( i = 0; i < hrLastColumn-1; i++){
        for ( j = 0 ; j < hrFirstColumn-1; j++){
        if(rangeValues[j][i] === “”){
        sheet.getRange(hrIndex,i+hrFirstColumn).setBackground(“#ea9999”);
        var columnIndexRange = sheet.getRange(hrIndex,i+hrFirstColumn);
        var columnIndex = columnIndexRange.getColumn();
        sheet.hideColumns(columnIndex);
        }else if (rangeValues[j][i] != “”){
        sheet.getRange(hrIndex,i+hrFirstColumn).setBackground(“#b6d7a8”);
        var showColumnIndexRange = sheet.getRange(hrIndex,i+hrFirstColumn);
        var showColumnIndex = showColumnIndexRange.getColumn();
        sheet.showColumns(showColumnIndex);
        };
        };
        };
        };

        Any and all help is greatly appreciated!

        Thom

        Reply

  8. Hello Yagi,

    I think I got it! The following will find the range and loop through the values regardless of how big or where it’s located. I mainly changed this line ” var rangeValues = hrArray[0];” and then I removed the second loop for “j”.

    Thom

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(‘Sheet1’);
    var hrRange = sheet.getRange(‘headerRow’);
    var hrIndex = hrRange.getRow();
    var hrFirstColumn = hrRange.getColumn();
    var hrLastColumn = hrRange.getLastColumn();
    var hrArray = hrRange.getValues();
    var i;

    function hideColumns2() {
    // Get array of values in the search Range
    var rangeValues = hrArray[0];

    // Loop through array and if condition met, add relevant
    // hide or unhide column.
    for ( i = 0; i < hrLastColumn-hrFirstColumn+1; i++){
    if(rangeValues[i] === “”){
    sheet.getRange(hrIndex,i+hrFirstColumn).setBackground(“#ea9999”);
    var columnIndexRange = sheet.getRange(hrIndex,i+hrFirstColumn);
    var columnIndex = columnIndexRange.getColumn();
    sheet.hideColumns(columnIndex);
    }else if (rangeValues [i] != “”){
    sheet.getRange(hrIndex,i+hrFirstColumn).setBackground(“#b6d7a8”);
    var showColumnIndexRange = sheet.getRange(hrIndex,i+hrFirstColumn);
    var showColumnIndex = showColumnIndexRange.getColumn();
    sheet.showColumns(showColumnIndex);
    };
    };
    };

    Reply

  9. Hi Yagi,
    Following your logic about the “good way” to retrieve the values, wouldn’t it also be a “better way” to set the new values, if you set them all in one server call, rather than many?
    Couldn’t you (conceptually) set the new values in your array, and then after the loop, send the whole array back to the server at once?

    (Maybe it’s more difficult when, in your example, you want to set cell properties or conditions, not just values. Would you agree with me, though, if all you wanted was to update the cell values/contents?)

    Reply

    1. Hi knwpsk,

      For sure, you could use setBackgrounds to input an array of values into the sheet in one hit. The problem here is that you also have to update all the cells that don’t need a background fill and even sending this back in a batch would be a little time costly. I think the aim of the post (it was 3 years ago now) was to highlight that you are better of getting the entire range from the Sheet and then iterating it through the Apps Script engine instead of making individual calls to the sheet for each cell. I don’t think I was sure at the time if I wanted to add returning the data in the same way and I may have tested the batch approach and found it slower because I had to add white background values as well.
      Anyway, it’s worth a revisit.

      Thanks,
      ~Yagi.

      Reply

      1. Makes sense.
        Returning the values individually or in bulk is worth considering, but should be optimized depending on the number of updates to be made.
        Thanks Yagi.

        Reply

  10. Good evening Yagi! I really appreciate this post, and it has helped me immensely. One problem I seem to be having now is looping through arrays where there are many empty cells (or so I think that is my issue). I get one of a couple errors:
    A) TypeError: Cannot read property ‘1’ of undefined
    – This seems to occur when I set i and j equal to 1
    B) Exception: The starting row of the range is too small
    – This seems to occur when I set i and j equal to 0
    Do you know where I am going wrong with this?
    Thank you for your time!

    function myFunction(){
    var s = SpreadsheetApp;
    var ss = s.getActiveSpreadsheet();
    var officeListSheet = ss.getSheetByName(“Office List”);
    var officeListDataRange = officeListSheet.getDataRange();
    var officeListVals = officeListDataRange.getValues();

    for ( i = 0 ; i < 20 ; i++){
    for ( j = 0 ; j < 20 ; j++){
    var customerFolderId = officeListSheet.getRange( i , j).getValue();

    };
    };

    };

    Reply

  11. Hi Yagi, thank you for the helpful content you put out there.

    I tried to follow this route for solving this question (see link below), but even than so, I run into a type error;

    ‘TypeError: Cannot read property ‘0’ of undefined’

    Probably has something to do with the different ranges we use, but shouldn’t the range be irrelevant in this piece of code, OR rather is there a way to revise this so it applies to all ranges? Maybe it’s not the range, and I’m wrong but I would love to hear your take on my question on stackoverflow below;

    https://stackoverflow.com/questions/66102557/check-column-header-of-a-range-and-operate-on-cells-of-another-range/66103116#66103116

    Reply

    1. Hi berkam,

      Marios solution looks pretty good and runs well. One slightly refactored version might be this:
      function NegativeCredit() {
      const creditIdx = DbCrValues.flat().indexOf("credit")
      let updatedCreditVals = TransxnValues.map(row => {
      let creditCell = row[creditIdx]
      creditCell = Math.sign(creditCell) == 1 ? -creditCell: creditCell;
      return row;
      });
      TransxnRange.setValues(updatedCreditVals);
      };

      If you are planning on having a bunch of formulas in your other columns of your ‘Transactions’ range, then I would recommend that you only update youor ‘Credit’ column. Otherwise, when you setValues() you will lose your formulas.

      const SS = SpreadsheetApp.getActiveSpreadsheet();
      const SHEET = SS.getSheetByName("Sheet10");
      const DbCrRange = SpreadsheetApp.getActive().getRangeByName("DbCrHdr");
      const DbCrValues = DbCrRange.getValues();
      const ROW_START = 3;
      const ROW_END = SpreadsheetApp.getActive().getRangeByName("Transactions").getNumRows();

      function NegativeCredit() {
      const creditIdx = DbCrValues.flat().indexOf("credit");

      //Get column range & values
      const creditColRange = SHEET.getRange(ROW_START,creditIdx+1,ROW_END);
      const creditColVals = creditColRange.getValues();

      let creditNegVal = creditColVals.map(row => [(row[0] = Math.sign(row[0]) == 1 ? -row[0]: row[0])]);
      creditColRange.setValues(creditNegVal);

      };

      ~Yagi

      Reply

      1. Thank you Yagi, I’m still a newbie with this language. Your concern is very valid, I do not want to operate on other cells other than the credit column, so thank you for that and all else ofcourse. Yet I have a hard time understanding the arrow functions and the question mark (if conditional ?), is there a way to revise so there aren’t any arrow functions or question marks? (If not, that’s totally fine but maybe there is a way to convert Mario’s at least…) I ought to learn those, I know, but an example to show the two versions can go a long way. Sorry to be asking, and not being able to assist.

        Reply

      2. Oh and one more thing, if there are more than 1 credit columns, how might I go about changing all of that? I’ve just changed the google sheet to the most updated one I use. How can I create an CreditIdx array that would get all the column indexes into one array and act on those; meaning I liked your own approach which doesn’t touch the formulas inside other columns; say for example the balance. Thank You!

        Reply

  12. Thanks for that excellent tutorial. I was looping through a huge sheet to get some information depending on several other values, and I went down from 30 seconds to just 1 second. I’m wondering how you managed to time your results. I’m using my phone, it’s not very precise.

    Reply
  13. Pingback: Google scripts: How to put a range in setValue - Tutorial Guruji
  14. Pingback: Iterate through selected/active range - Tutorial Guruji

Leave a Reply