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.
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)
Sorry Martin, I asked about google spreadsheet syncing on SO (http://stackoverflow.com/questions/23070242/how-can-i-maintain-referential-integrity-between-two-google-spreadsheets) as well and never came up with any good solutions.
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.
Did you try using a range? “A1:d5”?
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);
}
}
}
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;
}
}
}