Skip to content

Iterating over a range in Google Spreadsheets

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.

Google Apps | moore |

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)

  3. 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.

  4. moore says:

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

  5. 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);
    }
    }
    }

  6. 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.