Error only appears when running script in Apps Script Editor

Question

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:

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

You can't run this function from the script editor without supplying the event object. — Cooper
– Cooper, Commented Apr 9, 2019 at 20:38
Possible duplicate of How can I test a trigger function in GAS? — tehhowch
– tehhowch, Commented Apr 9, 2019 at 21:28

Cooper · Accepted Answer · 2019-04-11 00:04:20Z

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

edited Apr 11, 2019 at 0:04

answered Apr 9, 2019 at 20:48

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Terri House Over a year ago

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.

Cooper Over a year ago

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 Over a year ago

And keep in mind that this code only runs when there is a physical editing of content by a user in column E

J. G. · Accepted Answer · 2019-04-09 20:33:41Z

0

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

answered Apr 9, 2019 at 20:33

J. G.

1,8302 gold badges16 silver badges27 bronze badges

5 Comments

Terri House Over a year ago

It's still giving me an error code TypeError: Cannot find function getSheetByName in object Sheet. (line 2, file "Code")

Cooper Over a year ago

How are you running it? And is the code that you're running the same as the code in your question?

Terri House Over a year ago

I tried it with the code that @J. G. gave me with the ``` SpreadsheetApp.getActiveSheet().getSheetByName("Juvenile weights");

Terri House Over a year ago

Just wanted to let you know I was able to get it to work @Cooper so thanks for all of your help with this.

Cooper Over a year ago

Please checkoff one of the answers. Your choice.

Collectives™ on Stack Overflow

Error only appears when running script in Apps Script Editor

2 Answers 2

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related