1

I am trying to run a script that auto-populates the date in column F whenever data is entered or changed in column E in a spreadsheet with multiple sheets.

I currently have a script which was written for me based on what I described but I was getting an error code. I went back to my original question and explained that this script was needed to run on a single sheet within a spreadsheet with multiple sheets. He gave me some additional information but this was his last answer "yes, you will need to replace event.source.getActiveSheet() with something like "getsheetbyname". I have tried everything that is within my limited knowledge to work with what he gave me but I am still getting an error code no matter what I try.

Here is the area that is giving me the error. 

var sheet = event.source.getsheetbyname();

Here is the error code I am receiving

TypeError: Cannot read property "source" from undefined. (line 2, file "Code")

I am aware that there needs to be the name of the sheet I am wanting the script to run on but I do not know how to code it to do so. The name of the sheet is "Juvenile weights"

The expected results should be when I add or change the data in column E it should auto-populate the current date into the adjacent cell in the adjacent column.

When I save the script and then run the script to make sure it's working, of course, it gives me the error code I described above. Then when I go back to that sheet after saving the script instead of auto-populating the date, now when I highlight a string of cells in a row it changes all the following cells to the same information I have in the first cell I started highlighting. Very odd!

The script + error code:

enter image description here

The code:

function onEdit(event) { 
  var sheet = event.source.getActiveSheet();

  // note: actRng = the cell being updated
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();

  if (cindex == 5) { // 1 == Column A, 2 == Column B, 3 == Column C, etc.
   var dateCol = sheet.getLastColumn();  
   var lastCell = sheet.getRange(index,dateCol);
   var date = Utilities.formatDate(new Date(), "EST", "MMM-dd-yyyy");
   lastCell.setValue("'" + date);
  }
}
tehhowch
  • 9,645
  • 4
  • 24
  • 42
Terri House
  • 13
  • 3
  • You can't run this function from the script editor without supplying the event object. – Cooper Apr 09 '19 at 20:38
  • 2
    Possible duplicate of [How can I test a trigger function in GAS?](https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas) – tehhowch Apr 09 '19 at 21:28

2 Answers2

1

I believe this does the same thing:

function onEdit(e) { 
  var sheet = e.range.getSheet();
  if (e.range.columnStart == 5) {
    sheet.getRange(e.range.rowStart,sheet.getLastColumn()).setValue("'" + Utilities.formatDate(new Date(), "EST", "MMM-dd-yyyy HH:mm:ss"));
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Here's the issue. He gave me a script that works for one sheet by itself. I wanted to use it within a Spreadsheet with multiple sheets and there's no way I can just copy that script and transfer it from the original sheet to the sheet I want to use it for. Using it alone it works fine but using it with the sheet I need it to work with, It does absolutely nothing. Which is why I am still here asking for help. – Terri House Apr 10 '19 at 23:48
  • If you're talking about the script in your answer under "The Code:" that will work on all sheets and it does about the same thing that mine does except that I like to take advantage of the data in the event block as much as possible to save time because this code has to run in 30 seconds. – Cooper Apr 10 '19 at 23:55
  • And keep in mind that this code only runs when there is a physical editing of content by a user in column E – Cooper Apr 10 '19 at 23:57
0

Is there a reason you aren't using SpreadsheetApp.getActiveSheet().getSheetByName("Juvenile weights");

J. G.
  • 1,922
  • 1
  • 11
  • 21