Transfer cod from Excel to Google sheet

vbscript

#1

Hi dears

Nowadays I try to learn JavaScript from your fantastic website because I want to work with google sheets more professional than ago. I write a code in excel but now I want to use that in google sheet, could anybody help me to translate that until my JavaScript become professional! :slight_smile:
my excel code :

Function ErrTrend(weight As Integer, ErrorCount As Integer)

weight = Abs(weight)

If ErrorCount <= 2 Then
    ErrTrend = weight * ErrorCount
Else
     ErrTrend = weight * 2
End If
    
    For i = 1 To ErrorCount - 2
        
     ErrTrend = ErrTrend + (weight + i)
    
    Next i
ErrTrend = -ErrTrend
End Function

#2

Probably better to get it from the horse’s mouth…

SERP: does google sheets permit scripting?


#3

Aside

For the fun of it, let’s port your VBScript over to JavaScript…

First off, since VBScript is actually not case sensitive we can switch keywords over to lowercase…

change case
function errTrend(weight as integer, errorCount as integer)
weight = abs(weight)
if errorCount <= 2 then
    errTrend = weight * errorCount
else
     errTrend = weight * 2
end if    
    for i = 1 to errorCount - 2        
     errTrend = errTrend + (weight + i)    
    next i
errTrend = -errTrend
end function

In the above edit all variable names are now camelCase, and the code is surprisingly close to looking like JavaScript. JS, like C, Java, PHP, etc. has replaced End and Next (which is essentially part of the block definition) with curly brace block definition. Let’s make that switch, now…

transition blocks
function errTrend(weight as integer, errorCount as integer) {
weight = abs(weight)
if errorCount <= 2 then {
    errTrend = weight * errorCount
} else {
     errTrend = weight * 2
}    
    for i = 1 to errorCount - 2  {      
     errTrend = errTrend + (weight + i)    
    }
errTrend = -errTrend
}

In the last step we’ll put the finish on the code. JS is dynamically typed so the static variable declarations can be removed; plus we need to parenthesize the parameters on if and for statements; with a touch up on each.

ECMAScript 5
function errTrend(weight, errorCount) {
    weight = Math.abs(weight);
    var errTrend;
    if (errorCount <= 2) {
        errTrend = weight * errorCount;
    } else {
        errTrend = weight * 2;
    }    
    for (var i = 1; i <= errorCount - 2; i++) {      
        errTrend +=  weight + i;  
    }
    errTrend = -errTrend;
}

#4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.