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
36 responses to “How can I use JavaScript in Google Sheets?”
sir , can you please a video on Java abbreviations and little explanation of that
very very great video
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
How to lock or protect cells after data entry or input in Google Sheet?
Great video, Mr.
Thanks for posting such an amazing and easy-to-follow tutorial!
Python also has a shortcut akin to i++.
It's: i + = 1
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?
This tutorial explains more JavaScript than the actual google sheets customization.
I was expecting tutorials on APIs and stuff not this.
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?
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! 🙂
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.?
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?
Your video is great!
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)
where can i get the information about the terms you use in scripts such as Array, value, .length etc.
Hi Timothy, can you use this to pull api data into a spreadsheet?
I need some help, my function won't show on the actual spreadsheet. Thanks so much for the helpful video.
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 !
Thanks so much!
Thanks for posting!
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!
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???
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 ?
Thank you Timothy. Excellent!
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
Very cool. I can't wait to try it then make it work for me.
Outstanding, thank you. Very clear, very helpful.
Thank you so much!
Very Nice.. Thanks alot…
Very helpful! My program is working now! Thanks so much!!!!
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
Hi Timothy, I wonder why do you add 1 to i?
awesome. thanks
Short and to the point. Thanks!
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