• Sunday , 22 October 2017

How can I use JavaScript in Google Sheets?

Code Canyon



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

3d Ocean

Related Posts

36 Comments

  1. Sanjay Singh
    October 6, 2017 at 22:58

    sir , can you please a video on Java abbreviations and little explanation of that

  2. Sanjay Singh
    October 6, 2017 at 22:58

    very very great video

  3. Adnan Chowdhury
    October 6, 2017 at 22:58

    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

  4. Adnan Chowdhury
    October 6, 2017 at 22:58

    How to lock or protect cells after data entry or input in Google Sheet?

  5. Japan Slideshow
    October 6, 2017 at 22:58

    Great video, Mr.

  6. Meng Zhu
    October 6, 2017 at 22:58

    Thanks for posting such an amazing and easy-to-follow tutorial!

  7. Josh Caswell
    October 6, 2017 at 22:58

    Python also has a shortcut akin to i++.

    It's: i + = 1

  8. Pedro Bruno Muller
    October 6, 2017 at 22:58

    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?

  9. Arpan Adhikari
    October 6, 2017 at 22:58

    This tutorial explains more JavaScript than the actual google sheets customization.
    I was expecting tutorials on APIs and stuff not this.

  10. Eli Suárez
    October 6, 2017 at 22:58

    I keep on getting MYSUM() as 0 but i made sure that the script was written exactly as it was shown. What am i doing wrong?

  11. Khel Pabilona
    October 6, 2017 at 22:58

    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. Anshu Khandelwal
    October 6, 2017 at 22:58

    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.?

  13. Sri
    October 6, 2017 at 22:58

    If I use the same code which you have used to find the length of an array, its showing "length" undefined. Why is this happening?

  14. jeeradate kaowkanchana
    October 6, 2017 at 22:58

    Your video is great!

  15. Pradeep Kumar Guduru
    October 6, 2017 at 22:58

    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)

  16. Alvin Lorenzo
    October 6, 2017 at 22:58

    where can i get the information about the terms you use in scripts such as Array, value, .length etc.

  17. HBS981
    October 6, 2017 at 22:58

    Hi Timothy, can you use this to pull api data into a spreadsheet?

  18. Christian Batchlor
    October 6, 2017 at 22:58

    I need some help, my function won't show on the actual spreadsheet. Thanks so much for the helpful video.

  19. JHatLpool
    October 6, 2017 at 22:58

    Good demonstration of basic, core principles.
    Google sheets is marvellous. Look at what a user can do and he doesn't even need his own PC/ laptop !

  20. Michael English
    October 6, 2017 at 22:58

    Thanks so much!

  21. Dennis Daniels
    October 6, 2017 at 22:58

    Thanks for posting!

  22. Bogdan Taut
    October 6, 2017 at 22:58

    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!

  23. KIRAN55
    October 6, 2017 at 22:58

    Hi timothy, i wanna know how to stop responses of spreadsheet forms for a particular period of time(i.e, 3 hours)…..is it possible???

  24. ChuaN
    October 6, 2017 at 22:58

    I dun get it i type exactly same as you
    and I kept getting this
    TypeError: Cannot read property "length" from undefined. (line 5, file "Code")
    Can you help me ?

  25. joe hernandez
    October 6, 2017 at 22:58

    Thank you Timothy. Excellent!

  26. Procv Tube
    October 6, 2017 at 22:58

    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

  27. TheCronnoisseur
    October 6, 2017 at 22:58

    Very cool. I can't wait to try it then make it work for me.

  28. Teague Chrystie
    October 6, 2017 at 22:58

    Outstanding, thank you. Very clear, very helpful.

  29. Benjamin Garmendia
    October 6, 2017 at 22:58

    Thank you so much!

  30. a14266
    October 6, 2017 at 22:58

    Very Nice.. Thanks alot…

  31. Jack Gnibus
    October 6, 2017 at 22:58

    Very helpful! My program is working now! Thanks so much!!!!

  32. Oscar Rodriguez
    October 6, 2017 at 22:58

    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

  33. Hong Ing
    October 6, 2017 at 22:58

    Hi Timothy, I wonder why do you add 1 to i?

  34. Antech_
    October 6, 2017 at 22:58

    awesome. thanks

  35. Brint Montgomery
    October 6, 2017 at 22:58

    Short and to the point. Thanks!

  36. Antonio Martorana
    October 6, 2017 at 22:58

    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 Comment

You must be logged in to post a comment.