0

I want to add a date value when a new row is added in Google spreadsheet.

I have seen the script on Add a 'creation date' value when a new line is edited, but I was not able to modify the code to my needs.

Which changes do I need to do in that script?

1

In the question you are linking to, the date stamping happens when a new line is edited. If you want the date to be inserted immediately when a new line is added, simply replace EDIT with INSERT_ROW:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "INSERT_ROW") { // <-- This is true when a new row is inserted
    fillEmptyRows(sheet);
  }
}

function fillEmptyRows(sheet) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
    var firstColumnValues = range.getValues();
    for (var i = 0; i < firstColumnValues.length; i++) {
      if (firstColumnValues[i][0] == "") { // Column 0 is A
         firstColumnValues[i][0] = new Date();
      }
    }
    range.setValues(firstColumnValues);
}

The script must be installed as an event handler: Click Resources -> Triggers -> Add new trigger, select the onModification method and select On change from the right-most dropdown menu.

NOTE: This does not seem to work yet if you have opted in to the new Google Spreadsheets.

  • Does this work in the new Google Spreadsheets as well? – Jacob Jan Tuinstra Dec 19 '13 at 12:32
  • D'oh, it seems not: docs.google.com/spreadsheets/d/… – Vidar S. Ramdal Dec 19 '13 at 12:40
  • First of thank you so much Julebukk for help but its not working for me – Adarsh Dec 19 '13 at 15:59
  • Below is what i did. Tools--> Script editor-->Pasted the code-->Resources-->Current project's triggers-->Chickhere to add one now-->then selected onModification, From spreadsheet, Onchange then clicked on Save-->then I clicked on onModication and then fillEmptyRows Below error displayed. TypeError: Cannot read property "source" from undefined. (line 2, file "Code"). TypeError: Cannot call method "getRange" of undefined. (line 9, file "Code"). I have checked by adding one new row its not coming. Have i missed anything, please suggest ASAP :( :( I have not changed to New googlesheet – Adarsh Dec 19 '13 at 16:02
  • The script runs automatically when the spreadsheet is edited. Did you press the Run button in the script editor? Don't do that - it will give you the error you're seeing. I have a working example spreadsheet, try to add a couple of rows to it, and (after a second or two) the A column will display the current date. – Vidar S. Ramdal Dec 19 '13 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.