Using the macro facility in Google Spreadsheets can be quite powerful, but I’ve found the documentation difficult to navigate.

For future reference, here is how to iterate over a range of cells that has been predefined (using a named range or in some other manner).

var rangeName = 'mailingdate';
var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(mailingDateRangeName);
var arr = mailingDateRange.getValues();
for (var i = 0; i < arr.length; i++) {
var rowVal = arr[i][0];
if (rowVal.length < 2) { // 2 because of spaces
continue;
}
//... process non empty rows.
}

Note that this only processes a range with one column (hence the arr[i][0]. Ranges with multiple columns are left as an exercise for the reader.

6 thoughts on “Iterating over a range in Google Spreadsheets

  1. Martin says:

    Hello Moore,

    Recently, I’ve tried to find the method of bidirectional content syncing between google spreadsheets. I read a guy of article as below, and put in the formula you mentioned. Perphaps, I’m brand new to scripting in Google Docs, it doesn’t work !

    ———————————————

    function onEdit()

    {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();
    var activeSheetName = ss.getActiveSheet().getSheetName();

    if( activeSheetName == “Sheet1” || activeSheetName == “Sheet2” )
    {
    //var activeCell = activeSheet.getActiveCell();
    //var activeCellinA1 = activeCell.getA1Notation();

    var rangeName = ‘mailingdate’;
    var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(mailingDateRangeName);
    var arr = mailingDateRange.getValues();
    for (var i = 0; i < arr.length; i++)
    {
    var rowVal = arr[i][0];
    if (rowVal.length < 2)
    { // 2 because of spaces
    continue;
    }
    //… process non empty rows.
    }

    if( mailingDateRangeName == “A” )
    {
    var activerangeValue = mailingDateRange.getValues();
    if( activeSheetName == “Sheet1” )
    ss.getSheetByName(“Sheet2”).getRange(“A”).setValue(activerangeValue);
    if( activeSheetName == “Sheet2” )
    ss.getSheetByName(“Sheet1”).getRange(“A”).setValue(activerangeValue);
    }
    }

    }

    ———————–

     

    so thanks in advance for any expertise that is offered! MUCH appreciated!

     

    (http://stackoverflow.com/questions/25255087/bidirectional-content-syncing-between-google-spreadsheets-with-range?noredirect=1#comment39349862_25255087)

  2. Martin says:

    Hero Moore,

    Many Thanks for your reply. As the article I posted, I’ve try one single cell which is successfully working on, but no idea how to make it happen by a range of cells, for instance A1 to D5.

  3. moore says:

    Did you try using a range? “A1:d5”?

  4. Martin says:

    I surely tried this…The original code is as below, and this code is just able to syc one single cell between two sheets, which I look forward to performing a range of cells, like A1:D5. I guess it need to be formulated a range or array but “getActiveCell”. I am still working on it !

    function onEdit()

    {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();
    var activeSheetName = ss.getActiveSheet().getSheetName();

    if( activeSheetName == “Sheet1” || activeSheetName == “Sheet2” )
    {
    var activeCell = activeSheet.getActiveCell();
    var activeCellinA1 = activeCell.getA1Notation();

    if( activeCellinA1 == “A1” )
    {
    var activeCellValue = activeCell.getValue();
    if( activeSheetName == “Sheet1” )
    ss.getSheetByName(“Sheet2”).getRange(“A1”).setValue(activeCellValue);
    if( activeSheetName == “Sheet2” )
    ss.getSheetByName(“Sheet1”).getRange(“A1”).setValue(activeCellValue);
    }
    }
    }

  5. Martin says:

    I’ve tried to edit it, and have no idea where it goes wrong…stuck on third line!

    function onEdit()
    {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetRange(targetName,int)
    var activeSheet = ss.getActiveSheet();
    var activeSheetName = ss.getActiveSheet().getSheetName();
    var tgSheet = ss.getSheetByName(targetName);
    var output;

    if(targetName == activeSheetName)
    {

    if( activeSheetName == “Sheet1” || activeSheetName == “Sheet2” )
    {
    if( activeSheetName == “Sheet1” )
    {
    output = “Error: target sheet is active sheet !!”;
    }
    else {
    switch(int)
    {
    case 1:
    output = getSheetByName(“Sheet2”).getDataRange().getValues();
    break;
    case 2:
    output = getSheetByName(“Sheet2”).getSheetValues(1, 1, getSheetByName(“Sheet2”).getMaxRows(), getSheetByName(“Sheet2”).getMaxColumns());
    break;
    default:
    output = “Choose int to be 1 or 2 !!”;
    }
    }
    return output;
    }
    }
    }

Comments are closed.


© Moore Consulting, 2003-2019