Excel to Google Docs - Script

Question

I have a spreadsheet that I currently use in MS Excel. I am trying to convert the sheet to a Google Docs spreadsheet, but am unable to get my excel script to function in google docs.

It's a simple accumulation script repeated across a row of cells:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      With Target
         If .Address(False, False) = "C3" Then
            If IsNumeric(.Value) Then
               Application.EnableEvents = False
               Range("C4").Value = Range("C4").Value + .Value
               Application.EnableEvents = True
            End If
         End If
      End With

If anyone can translate that to what I need in google docs, or even better improve on it so that the script covers the whole row (example: if cells c4, d4, and e4 have the values of 5, 7, 12 respectively and I put into c3: 10, then c4 would show 15; if I put into d3: 6, then d4 would be 13 and if I put 7 into e3, then e4 would show 19).

To be honest, I'm not too keen on Javascript. If I try to plug it in as is, I receive the following: Missing ; before statement. (line 1, file "Code")Dismiss — user2347530
I'm fairly certain that Google Docs uses Javascript and not Visual basic. The above script is a VB script in excel and functions as expected, when I attempt to place the script into a google doc spreadsheet it does not translate. being unfamiliar with Javascript coding, I'm not certain on how to correct what I currently have programmed. — user2347530
I'm voting to close this question as off-topic because the questioner just wants us to translate the code from vbscript to javascript. — Cooper

Mogsdad Mogsdad · Accepted Answer · 2013-12-27T16:46:27

You can't directly translate a VBA function to Google Apps Script, but if you take the VBA as a specification, you can usually create a similar function. Note that many of the capabilities provided in Excel VBA are specific to the UI and the Windows environment, and have no equivalent in GAS (Application.EnableEvents for example).

In this case, the VBA function appears to be responding to changes in a specific cell in the spreadsheet. In GAS, we can use an onEdit trigger function instead. The trigger function will be provided with event information that we can decode to figure out where the edit occured, and what the value in the changed cell was. See Understanding Events, especially Spreadsheet Edit Events for more about that.

Here's how I think the VBA function would appear in Google Apps Script. I've borrowed a helper function, isNumber(), from Validate decimal numbers in JavaScript - IsNumeric() to simplify the validation of cell C3's contents.

function onEdit(event) {
  if (event.range.getA1Notation() == "C3") {
    if (isNumber(event.value) {
      // C4 = C4 + C3
      var c4 = event.range.offset(0,1); // one cell to the right
      var c4Val = c4.getValue() + event.value;
      c4.setValue(c4Val);
    }
  }
}

// From stackoverflow.com/a/1830844/1677912
function isNumber(n) {
  return !isNaN(parseFloat(n)) && isFinite(n);
}

Excel to Google Docs - Script

2 Answers