thanks , can you explain if possible how to add something in every new comming line in sheet , as i write 33 in one line and it automatically add 1 in previous line and write result in new cell. thanks if you can help.
Very understandable. I have one question. I want to fetch live data from a website and refresh the data in sheets every one minute. I used importHtml to import data but when i use settimeout method of javascript to refresh , it shows undefined. Could you please help me how to do it ?
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!
That's easy - you just type this into the cell: = B3 - C3 After you do that, you can copy and paste the value from that cell into several others and the values will adjust accordingly.
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.?
You could install a trigger that will do this; gist.github.com/andrewroberts/21bc8b1b3fc7d3b40e6b should give you an idea of how to export the PDF and developers.google.com/apps-script/guides/triggers/installable should show you how to use installable triggers to do this automatically.
what should be the code if I want to track the last time a cell or a column change / edit happened on a particular cell named "Last Update" for example.
If you write a function called onEdit, that should allow you to determine which cell was edited and identify when it was updated. See this video: ruclips.net/video/L1_nIhiVc5M/видео.html
how do i use javascript to check if there is a particular value in sheet 2 of the same google sheet file and if it is, an action will be performed adding a value to sheet 1
To get a particular value in the current spreadsheet, try this (this would get cell A2): SpreadsheetApp.getActiveSheet().getRange(2, 1).getValue(); To get a particular value in a *named* sheet, try this (this would get cell B5 on "Sheet 2"): SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 2").getRange(5, 2).getValue(); As far as actions, you can append values to your current sheet using this call: var values = [2, 3, 4, 5]; SpreadsheetApp.getActiveSpreadsheet().appendRow(values);
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
It should work if you use it as a function in your spreadsheet. It won't work if you run it from the script editor, because it's just going to call the function (without a parameter). Try changing it to this: function MYO(values) { var result; if (values) { result = values.length; } else { result = null; } return result; } That way it will run even if you don't pass in any values.
You should be able to protect the sheet with Apps Script: developers.google.com/apps-script/reference/spreadsheet/protection You could implement the onOpen trigger (see developers.google.com/apps-script/guides/triggers/) and have it automatically protect / unprotect the spreadsheet.
I have the same issue. Very the same as the code provided in the video, but have error of the "length" is undefined. no one is answering this problem yet
The answer might not be so obvious. It's quite possible that you're not passing in a range to MYSUM - which means that "values" would be undefined. Take another look at the code and how you're calling the function, as it is very particular about syntax.
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)
I think you're asking about the i++ in the for-loop. We do this so that each iteration has a different value of i - it allows the for-loop to count from 0 to the number of values we're analyzing. Does that answer the question, or can I provide more info?
***** You mean i is not constant. So, once you add 1 to i, i will increase by 1 through the list of number given. Let's say I have 2,3,4,5. When I use i+1, then I will get, 3,4,5,6. Is this correct?
Yes, but the i++ executes at the end of the loop. So you start with 0, then, 1, then 2, etc. If you're talking about the code at ruclips.net/video/DRmWYChhhLk/видео.html, the while loop doesn't have an increment step, so you have to execute i++ or i = i + 1 separately.
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 ?
At what point in the video are you having a problem? It sounds like you're accessing "length" before the object / array is created. Can you share more of the code?
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 () + "'" + " ") ; } 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 () + "'" + " ") ; } What am I doing wrong?
It's hard to say without seeing the rest of the code. I'm guessing that there are some things missing from the copy & paste - I'm seeing the use of a variable called CurrentDate throughout this script you posted, but I'm not seeing any declaration or initialization of that variable. That could be the problem.
I used to love the ability to script Google Sheets, but its nearly 2020 now and the version of JavaScript that Google supports is incredibly ancient now and the online editor is terrible. I find it very frustrating.
Hi Timothy. You seem like the guy that might be able to help me out! I’m a complete newbie at this spreadsheet stuff, but I’m trying find someone that can help me import just the first and last bit of data, from the first row of a table. I’d like someone to help me retrieve some info from this site: www.nasdaq.com/symbol/dpz/dividend-history I’m looking to only import the first date and last date from the first row. I’ve figured out this, so far: =importhtml("www.nasdaq.com/symbol/dpz/dividend-history","table",3) I see that it’s table 3, but I don’t want to import the entire table, just the first date in row one and another code for the last date in row one. Could you be so kind to help me create these 2 codes? Any help would be GREATLY appreciated!! THANK YOU!!
The easy way to do this would be to use the function as you're doing, but put it into a temporary sheet. Then, use Apps Script to retrieve the first row and the last row and put it where you like. By sequencing these calls and doing one thing after another, you should be able to accomplish what you need.
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
this was my first one where I got them all... 10 outta 10 will recommend
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 !
Very well explained, i thought it was at too basic a level for myself once i got the general idea, but I watched til the end.
thanks , can you explain if possible how to add something in every new comming line in sheet , as i write 33 in one line and it automatically add 1 in previous line and write result in new cell. thanks if you can help.
Can APIs also be manipulated in GSheets?
Thanks for posting such an amazing and easy-to-follow tutorial!
Is there any way to make a script with html the search data from spreadsheets and display result in HTML?
Take a look at this: www.freecodecamp.org/news/cjn-google-sheets-as-json-endpoint/
Timothy James thank you 🙏 for your reply. Is the a video demonstration for that?
@@abdullahquhtani4247 unfortunately I don't have one.
Thank you for your awesome tutorial
Very good job , can you help I would like to store a historical data of the pulling info that contain the data and time of fetching ?...thanks
I need some help, my function won't show on the actual spreadsheet. Thanks so much for the helpful video.
try adding @customFunction into your comment before the function.
its @customfunction and not @customFunction, learnt it the hardway!
function MYSUM (values) {
return values.map(parseFloat).reduce((a, b) => a + b)
}
Very helpful! My program is working now! Thanks so much!!!!
Python also has a shortcut akin to i++.
It's: i + = 1
Josh Caswell javascript has i+=1;
python you could simply just do:
total = 0
for value in values:
total += value
Nice Work there. Simple and educating
yep I needed that knee to the boys... good job Sr. Great vid loved it
Very understandable. I have one question. I want to fetch live data from a website and refresh the data in sheets every one minute. I used importHtml to import data but when i use settimeout method of javascript to refresh , it shows undefined. Could you please help me how to do it ?
You can probably use Utilities.sleep - developers.google.com/apps-script/reference/utilities/utilities#sleep(Integer)
where can i get the information about the terms you use in scripts such as Array, value, .length etc.
The W3schools JavaScript tutorial is a good start: www.w3schools.com/js/
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!
That's easy - you just type this into the cell:
= B3 - C3
After you do that, you can copy and paste the value from that cell into several others and the values will adjust accordingly.
sir , can you please a video on Java abbreviations and little explanation of that
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.?
You could install a trigger that will do this; gist.github.com/andrewroberts/21bc8b1b3fc7d3b40e6b should give you an idea of how to export the PDF and developers.google.com/apps-script/guides/triggers/installable should show you how to use installable triggers to do this automatically.
what should be the code if I want to track the last time a cell or a column change / edit happened on a particular cell named "Last Update" for example.
If you write a function called onEdit, that should allow you to determine which cell was edited and identify when it was updated. See this video: ruclips.net/video/L1_nIhiVc5M/видео.html
how do i use javascript to check if there is a particular value in sheet 2 of the same google sheet file and if it is, an action will be performed adding a value to sheet 1
To get a particular value in the current spreadsheet, try this (this would get cell A2):
SpreadsheetApp.getActiveSheet().getRange(2, 1).getValue();
To get a particular value in a *named* sheet, try this (this would get cell B5 on "Sheet 2"):
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 2").getRange(5, 2).getValue();
As far as actions, you can append values to your current sheet using this call:
var values = [2, 3, 4, 5];
SpreadsheetApp.getActiveSpreadsheet().appendRow(values);
Short and to the point. Thanks!
Hi Timothy, can you use this to pull api data into a spreadsheet?
You can but it's not super simple to explain - I'll be recording some new videos soon and will probably touch on this.
Kindly check out this video ruclips.net/video/hL4I6cyE5TA/видео.html to pull API data from web and import to spreadsheet
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
It should work if you use it as a function in your spreadsheet. It won't work if you run it from the script editor, because it's just going to call the function (without a parameter). Try changing it to this:
function MYO(values) {
var result;
if (values) {
result = values.length;
} else {
result = null;
}
return result;
}
That way it will run even if you don't pass in any values.
Thanks!!! regards
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???
You should be able to protect the sheet with Apps Script: developers.google.com/apps-script/reference/spreadsheet/protection
You could implement the onOpen trigger (see developers.google.com/apps-script/guides/triggers/) and have it automatically protect / unprotect the spreadsheet.
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?
I have the same issue. Very the same as the code provided in the video, but have error of the "length" is undefined. no one is answering this problem yet
The answer might not be so obvious. It's quite possible that you're not passing in a range to MYSUM - which means that "values" would be undefined. Take another look at the code and how you're calling the function, as it is very particular about syntax.
Great intro to custom functions!
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)
I'm not sure I follow. This seems like something you could do with regular formulas in Google Sheets.
Hi Timothy, I wonder why do you add 1 to i?
I think you're asking about the i++ in the for-loop. We do this so that each iteration has a different value of i - it allows the for-loop to count from 0 to the number of values we're analyzing. Does that answer the question, or can I provide more info?
***** You mean i is not constant. So, once you add 1 to i, i will increase by 1 through the list of number given. Let's say I have 2,3,4,5. When I use i+1, then I will get, 3,4,5,6. Is this correct?
Yes, but the i++ executes at the end of the loop. So you start with 0, then, 1, then 2, etc.
If you're talking about the code at ruclips.net/video/DRmWYChhhLk/видео.html, the while loop doesn't have an increment step, so you have to execute i++ or i = i + 1 separately.
I got it now. Thank you so much.
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?
hard to tell without seeing the code; can you paste it here?
how do you update sheet without clearing a row
I'm not sure what you mean - can you clarify the question?
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 ?
At what point in the video are you having a problem? It sounds like you're accessing "length" before the object / array is created. Can you share more of the code?
Outstanding, thank you. Very clear, very helpful.
Very cool. I can't wait to try it then make it work for me.
hi , can your help me make a couple of first seconds
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 () + "'" + "
") ;
}
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 () + "'" + "
") ;
}
What am I doing wrong?
The error is:
Missing ; before statement. (line 5, file "Code"
It's hard to say without seeing the rest of the code. I'm guessing that there are some things missing from the copy & paste - I'm seeing the use of a variable called CurrentDate throughout this script you posted, but I'm not seeing any declaration or initialization of that variable. That could be the problem.
This is for pc only? how can i use phone run this? don,t work TvT
Well, it will work on a Chromebook or Macbook, but unfortunately buttons won't function on a tablet (or a phone).
Thank you Timothy. Excellent!
joe hernandez I'm trying to teach 5000 people to code this year. Check out 5000coders.com for more info.
I used to love the ability to script Google Sheets, but its nearly 2020 now and the version of JavaScript that Google supports is incredibly ancient now and the online editor is terrible. I find it very frustrating.
Good news, V8 runtime is supported as of early Feb: developers.google.com/apps-script
Good tutorial, thnx!
Thanks for video!!!
thank you
Thanks for posting!
Very Nice.. Thanks alot...
Your video is great!
I NEED THE APLICATION IN SPANISH.
Ah, unfortunately no hablo español.
great video. thanks
thank you!
very very great video
Thank you so much!
awesome. thanks
Hi Timothy. You seem like the guy that might be able to help me out! I’m a complete newbie at this spreadsheet stuff, but I’m trying find someone that can help me import just the first and last bit of data, from the first row of a table.
I’d like someone to help me retrieve some info from this site: www.nasdaq.com/symbol/dpz/dividend-history
I’m looking to only import the first date and last date from the first row. I’ve figured out this, so far:
=importhtml("www.nasdaq.com/symbol/dpz/dividend-history","table",3)
I see that it’s table 3, but I don’t want to import the entire table, just the first date in row one and another code for the last date in row one. Could you be so kind to help me create these 2 codes? Any help would be GREATLY appreciated!! THANK YOU!!
The easy way to do this would be to use the function as you're doing, but put it into a temporary sheet. Then, use Apps Script to retrieve the first row and the last row and put it where you like. By sequencing these calls and doing one thing after another, you should be able to accomplish what you need.
Dan, WHAT ARE YOU DOING
WHO IS DAN
Thank you very much!!!
Thanks so much!