D4mations.com

How can I use JavaScript in Google Sheets?



In Google Sheets, we can use Apps Script (JavaScript) to create custom functions. In this video we show how we can use just a bit of code to create a custom function that gives us more control and ability to create more complex functions.

A great guide for working with custom functions in Google Sheets is available here: https://developers.google.com/apps-script/guides/sheets/functions

An overview of Google Apps Script can be found here: https://developers.google.com/apps-script/overview (note that JavaScript is almost identical to Apps Script)

Follow me at https://twitter.com/geekitarian_ed and check out http://coding4.us/ for more coding tutorials!

If I’ve helped you learn to code in 2017, please go to http://5000coders.com and let me know!

Original source

d4mer

, , , ,

36 responses to “How can I use JavaScript in Google Sheets?”

  3. is it possible to use in Google sheet:-

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = 2 Then
    confirm = MsgBox(“Do you wish to confirm entry of this data?”
    & vbCrLf & “You will not be allowed to change it!”, vbYesNo, “confirm Entry”)
    Select Case confirm
    Case Is = vbYes
    Dim Cell As Range
    With ActiveSheet
    .Unprotect Password:=”asdf,1234″
    .Cells.Locked = False
    For Each Cell In ActiveSheet.UsedRange
    If Cell.Value = “” Then
    Cell.Locked = False
    Else
    Cell.Locked = True
    End If
    Next Cell
    .Protect Password:=”asdf,1234″
    End With
    Case Is = vbNo
    Application.Undo
    End Select
    End If
    Application.EnableEvents = True
    End Sub

  8. Hi Timothy James,

    I am trying to create a formula for a column return the date that the register was created ( if I created a row when this data was generated)

    I found this, however, I still struggling to do it.
    var DateFormat = "mm/dd/yyyy;
    var StatusColumn = 8 ;
    var ResolvedColumn = 9 ;

    var CurrentDateString = Utilities.formatDate (CurrentDate, Session.getScriptTimeZone(), DateFormat) ;
    var StatusCell = sheet.getRange (row, StatusColumn) ;
    var ResolvedCell = sheet.getRange (row, ResolvedColumn) ;

    ResolvedCell.setValue (CurrentDate) ;
    ResolvedCell.setNumberFormat (DateFormat) ;
    StatusCell.setNote (StatusCell.getNote () + CurrentDateString + " – changed to '" + StatusCell.getValue () + "'" + "rn") ;
    }

    However, then I am creating the function, the script is shown me an error:

    Function ADDDATES() {

    var DateFormat = "mm/dd/yyyy;
    var StatusColumn = 8 ;
    var ResolvedColumn = 9 ;

    var CurrentDateString = Utilities.formatDate (CurrentDate, Session.getScriptTimeZone(), DateFormat) ;
    var StatusCell = sheet.getRange (row, StatusColumn) ;
    var ResolvedCell = sheet.getRange (row, ResolvedColumn) ;

    ResolvedCell.setValue (CurrentDate) ;
    ResolvedCell.setNumberFormat (DateFormat) ;
    StatusCell.setNote (StatusCell.getNote () + CurrentDateString + " – changed to '" + StatusCell.getValue () + "'" + "rn") ;
    }

    What am I doing wrong?

  11. Hi, is it possible to add keyboard events in google sheets? I actually want to add a customer function that triggers the shortcut key of "ctrl+shft+:" to log the time on a targeted cell each time I press/click an image button in the google sheets. I hope you could enlighten me on this one. God bless you Sir! 🙂

  12. Hi Timothy ,
    Thanks for Posting Nice Video , I have got question regarding downloading current google sheet to my local computer drive.
    Do you have an idea how can I download the current Active Sheet using google Script or Java Script without user intervention.?

  15. How to Get Values in each rowof Column A and then Set values in corresponding rows of Column B in Spreadsheet Using Google App Script
    For Example
    Column A
    HDFC
    HSBC
    AMEX
    SBIN
    BOB

    Column B (need to set values in corresponding rows of Column B)

  22. Hello,
    What would the script (syntax) be if I would like to subtract two cells sitting next to each other…say columns B3 and C3.(the entire column)I do not want to use the built in function from google sheets just to create a new subtract function.
    Thanks!

  26. Amazing!!!!!!

    I'm here from Brazil and the company that i work change to Google for Works.!
    Now i'm trying to understand the Google Script in Sheets and all the products of Google.

    Do u have some playlist here, site, blog or something with a sequence video to help me??

    I'm now ready a programmer (VBA).

    Tks!!!!

    My contact is contatoprocvtube@gmail.com

  32. Hi Timothy i try to execute the command

    function MYO(values) {
    return values.length;
    }
    but always give me the same error message

    TypeError: Cannot read property "length" from undefined. (line 5, file "Code")

    What's the problem???
    Thanks

  36. Hi Timothy I need your help … how can I add a button on a sheet that i can use to add or take off a specific value on a cell , for example if i got an inventory sheet and i want to add or subtract the quantity on hand of that item total , i don't want to highlight the item total and change the number, I want to do it just by clicking the (+) or (-) button that i create… can that be done on google sheets? …. You can reach me at agm667@gmail.com Thank you

Leave a Reply