/docs/community?hl=en
/docs/community?hl=en
11/26/17
Original Poster
Ben Downton

VBA to Google script converter

Hey

I have a document in Excel which uses a Macro and I would like to convert to Google Docs so I can share it with a friend who uses a mac.

unfortunately, this means the Macro would need to be converted to Google script and I've had trouble getting the script to work.


What I want is when columns A, C & E have data imputed in then the adjacent cell would have a time & Date stamp added

This is my VBA Script from Excel

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then
    On Error Resume Next
    Application.EnableEvents = False
        Target.Offset(, 1) = Now
    Application.EnableEvents = True
    On Error GoTo 0
    End If
End Sub

Hopefully, someone can help :)

Ben


Community content may not be verified or up-to-date. Learn more.
All Replies (3)
GimelG
11/26/17
GimelG
Hi Ben,

Try this:

function onEdit(e) {
  var s = e.source.getActiveSheet(),
      r = e.range,
      col = r.columnStart,
      cols = [1,3,5];
  if (cols.indexOf(col) === -1) return;
  s.getRange(r.rowStart, col+1).setValue(new Date());
}


Cheers,
G
11/26/17
Original Poster
Ben Downton
Seems to work just fine, thanks so much for your help :D
GimelG
11/26/17
GimelG
This may be slightly faster:

function onEdit(e) {
  var r = e.range,
      col = r.columnStart;
  if ([1,3,5].indexOf(col) === -1) return;
  e.source.getActiveSheet().getRange(r.rowStart, col+1).setValue(new Date());
}

Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Expert - Google Employee — Googler guides and community managers
 
Expert - Community Specialist — Google partners who share their expertise
 
Expert - Gold — Trusted members who are knowledgeable and active contributors
 
Expert - Platinum — Seasoned members who contribute beyond providing help through mentoring, creating content, and more
 
Expert - Alumni — Past members who are no longer active, but were previously recognized for their helpfulness
 
Expert - Silver — New members who are developing their product knowledge
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.