CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggersgoogle-apps-script-editor
Terri House
Terri House

Reputation: 13

Error only appears when running script in Apps Script Editor

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

Upvotes: 1

Views: 518

Answers (2)

Cooper
Cooper

Reputation: 64140

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"));
  }
}

Upvotes: 1

J. G.
J. G.

Reputation: 1832

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

Upvotes: 0

Related Questions