Iterate a function through several rows in a Google Spreadsheet

Question

Here is the script I have written. It puts a time stamp at the end of the row whenever that row is edited.

function setDate() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
  //Get Active cell
      var mycell = ss.getActiveSelection();
      var cellcol = mycell.getColumn();
      var cellrow = mycell.getRow();
  //Check to see if column is A or B to trigger
      if (cellcol == EDITMECOLUMN)
      {
  //check for row to trigger
        if (cellrow == EDITMEROW)
        {
  //Find cell and set date in a defined cell
      var celldate = sheet.getRange(EDITMEROW, EDITMECOLUMN);
      celldate.setValue(new Date());
  //end set date
        }
      }
 }

It works well, but how can I change it so it iterates for each row instead of me having to copy the script for each row manually? I have a form that populates a spreadsheet, and the "latest" results from each topic in the form are put in a report spreadsheet. I need the column time stamps for each row because each row represents a topic from the form, and the form user can choose to skip sections for weeks at a time.

EDITMExx pieces are just spots where I would put column or row #.

The function is called from a onEdit() trigger

More information is needed before a rational answer can be provided. What is EDITMEx? How are you calling this `setDate()`? Is it an onEdit trigger function, a custom spreadsheet function, or something else? What use case do you need to resolve that would put a timestamp on multiple rows for a single edit? Add more information to this question, please. — Mogsdad, May 15 '13 at 17:04
I think we're getting thrown off by the word 'iterates'. Do you mean that you want a timestamp *on each row when that row is edited*, or do you want a timestamp *repeated on every row when any row is edited*? — Mogsdad, May 15 '13 at 17:59
The first one, I want a timestamp on each row when that row is edited :) — jredeker, May 15 '13 at 18:50

score 2 · Answer 1 · answered May 15 '13 at 14:04

2

Create a for-loop (or any kind of loop) that will iterate through all your rows. Something like:

var numRows = sheet.getDataRange().getNumRows();
var data = sheet.getDataRange().getValues();
for(var i=0; i<numRows; i++){
    data[i][someColumnWhereYouWantDateSet].setValue(new Date());
}

answered May 15 '13 at 14:04

rGil

3,719
1
22
30

Where in my function should I put the for-loop? I tried within the if statement but that did not work. – jredeker May 15 '13 at 15:13

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

Since you're calling this function from an onEdit trigger, you can (should!) take advantage of the Event that is generated to know where to put the timestamp. (See Understanding Events.)

You state that you place a timestamp "whenever that row is edited". I'll assume that the timestamp column is fixed, like so: var TIMESTAMP = xx, where xx is the column number.

/**
 * onEdit function to place timestamp in a specified column
 * when an edit is made in a row.
 * 
 * @param {Event Object} event  Information about the event
 *                              that caused this function to
 *                              be triggered.
 */
function setDate(event) {
  var TIMESTAMP = 5;   // Column number for timestamp
  var HEADER_ROWS = 1; // # of header rows to skip monitoring

  var ss = event.source.getActiveSheet();
  var changedRange = event.source.getActiveRange();
  var changedRow = changedRange.getRow();

  //Logger.log("Edited range:" + changedRange.getA1Notation());
  if (changedRow > HEADER_ROWS) {
    ss.getRange(changedRow,TIMESTAMP).setValue(new Date());
  }
  // else do nothing
}

This is a pretty basic onEdit function, with just one conditional check - it avoids timestamping your headers. You can add other tests to monitor a subset of columns, specific sheets, and so on. Knowing what objects are passed in the Event parameter will help you imagine other possibilities.

Warning: There are lots of ways that you can miss changes to your spreadsheet. See this answer for more information.

If you're new to Apps Script triggers, see How can I test a trigger function in GAS? for debugging tips.

edited May 23 '17 at 12:04

Community

1
1

answered May 15 '13 at 18:07

Mogsdad

44,709
21
151
275

Sorry, one issue I realized I still have with this. It works perfectly when I am directly editing a cell, but let's say that cell B1 is set to =D1. When I edit cell D1, cell B1 is changed, but the date is not updated for row B. Any suggestions? – jredeker May 16 '13 at 14:56
Ah, but in that case you didn't edit row B, the content changed due to a formula - [one of the things that don't get picked up by onEdit triggers](http://stackoverflow.com/a/14794528/1677912). One thing you can do, if that's the behavior you want, is to maintain two copies of every row's contents - the one in the spreadsheet, and a second "old" copy - and then on ANY change, iterate over all rows looking for content changes and marking them. – Mogsdad May 16 '13 at 15:09
...For a small sheet, this would be ok, but would make edits very very slow for even a modest size of data. Another idea, you could build and maintain a map of formula relationships, and walk through that on every change to note which cells are updated. Less data-intensive, but more computationally complex. I guess what I'm saying is that you're not going to be able to rely on an onEdit trigger to mark formula changes. – Mogsdad May 16 '13 at 15:10
"you could build and maintain a map of formula relationships, and walk through that on every change to note which cells are updated." What would this option look like? Would it be something like, a formula with an if statement to see if the "history" cell equaled the "formula-edited cell" and if not it would set the value of the current date? – jredeker May 16 '13 at 16:21
Kind of... I was imagining that you'd `getFormulas()` on the whole spreadsheet, and parse those to determine cell relationships. From that, create a table that would tell you "if D changes, B will change". Then, when onEdit tells you D changed, you'd know to timestamp B as well. Sounds easy - but for a general solution this would be very, very tricky. – Mogsdad May 16 '13 at 16:29

Iterate a function through several rows in a Google Spreadsheet

2 Answers2