I love how you give concise and relevant information information in your tutorial. You mentioned not to make multiple calls to the same spreadsheet. That's what I did, and I think that's why my script is running a bit slow. Thank you so much for this amazing video!
You're welcome Andy. I believe that teaching is not only about showing how something is done but also explaining why. Only if you understand the why can you use this knowledge in other situations or projects.
Thanks, I am looking forward to make automation where I will copy n paste huge data and when i press a button, data will be sent row by row as a POST request to an API endpoint. Would love to know your views about this.
Suppose we have some fields on google forms and we want to go through each field throgj app for forms. Suppose we have name and email id. We want to send email throgh forms submit. How we can do through google app script
Hi Chanel I'm new to app scripts here, can't the forEach function just edit the "values" array where you retrieved the table data from the getValues() and use the [values] to range.setValue([values])
No, you shouldn't do that. The values you got in one go have to be changed and then you set the new values in the sheet. This is a batch operation. Otherwise you would have to get the values cell by cell, change them and write them directly back to the sheet. Your script would take forever to run and would most likely stop because of a timeout.
HI. I think I'm more confused about this function. I think you explained some things in greater detail than I know about the loop. I don't understand about the function part of the loop and especially the singular and plural thing. I thought the name 'value' was unrelated to the variable 'values' in script. Hope I have relayed my confusion sufficiently. I will search your other videos. Thanks
OK, so i didn't realise before i think you were running the function inside the '(callback)' section rather than have a separate function where you pass the argument to.... did i get that right or am i still lost? thanks
I came accross a couple of your tutorials and found them very helpful in AppsScript. I'm now facing a problem with looping (or repeating) the same scrips in every sheet in my spreadsheet. Here's the code that I'm dealing with down below. Both codes are valid, but the one which is supposed to work within all the sheets doesn't really work at all. Could you please advise how to solve this? function hideRow1() { var ss = SpreadsheetApp.getActive(); var sheet = ss.getSheetByName("TEMPLATE_1"); var dropDown = sheet.getRange('B34:C34').getValue(); if(dropDown == "alphaJET") { sheet.showRows(37,75); sheet.showRows(203,6); } else if(dropDown == "laserSYSTEM") { sheet.showRows(37,75); sheet.showRows(203,6); } else { } } function alltabs(){ var spreadsheet = SpreadsheetApp.getActive(); var allsheets = spreadsheet.getSheets(); allsheets.forEach(function(sheet){ if(sheet.getSheetName() !=="Arkusz4"){ sheet.activate(); hideRow1(); } }) }
Yes when using template literals, to output the value of a variable together with a string, you need to use backticks. If you use single quotation marks it won‘t work.
i need a script for there is one column which is some numbers write like 2 ,3,4,5 but condition is if there is mention number of 4 so till next five column print number till 5 (like 1,1,1,1,1) it is possible so give me the script
Hi, thanks for the video. I'm new in Java and I'm having some issues. Every month I should analyze a file with multiple and randon amount of sheets(with different names) with a macro. Here's the thing: I found out that work with their indexes would be easier, however, when I did a for lace just to run the script for all sheet's index and when I also tryed to work all the sheets by using .forEach(function(sheet)), it didn't run the code. It shows a error saying that .getRange() is not a function(I needed to use a letter of the alphabet inside the ''getRange(lletter, number)'', to implement the rows from another loop). I think I'm getting confuse with Java and Python. Plus I didn't found the documentation that says if there is a way to test/get the amount of sheets. Do you have any clue of where can I get this documentation?
Thanks for your comment. Just to clarify: Google Apps Script is essentially JavaScript. Java is another programming language and has nothing to do with Google Apps Script. If you want to find out how to use Google Apps Script then check this official documentation: developers.google.com/apps-script the getRange() method get's a specific range within a spreadsheet or a sheet. You can use this method by passing in the row and columns or A1 notation. Please check the reference to find out how to use this method: developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer)
Hi Chanel :) Why a DO WHILE loop contained within a function (that I want run at the end of my script), completely stops all the previous functions? Is it possible?? Thanks
I don't know why your code is not working. In general a do...while loop will run at least once and until the condition evaluates to FALSE. Here some more information on this type of loop: developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/do...while
@@saperis Ok. Imagine function ABBOTT() that writes “START” on a spreadSheet and then calls a function COSTELLO(). The function COSTELLO() does a DO… WHILE loop that writes “STOP” at a certain time (with new Date() object): it will run the loop until it reach the scheduled time. My problem is that function ABBOTT() writes “START” only when function COSTELLO() finishes the DO…WHILE loop. Why this?? (thanks)
Apps Script is executed line by line. So if you first have "Start" written to the spreadsheet and then call the COSTELLO function, that's the order it should run in. But I think you need someone to look at your exact code to pin point where the error is happening. I don't offer any code reviews or development as a matter of fact. Check here for a freelancer that can help you with your script: www.upwork.com/hire/google-spreadsheet-freelancers/
I love how you give concise and relevant information information in your tutorial. You mentioned not to make multiple calls to the same spreadsheet. That's what I did, and I think that's why my script is running a bit slow. Thank you so much for this amazing video!
You're welcome Andy. I believe that teaching is not only about showing how something is done but also explaining why.
Only if you understand the why can you use this knowledge in other situations or projects.
Sua didática é simplesmente incrível. Estou começando a dar meus baby steps no google app scripts e seus vídeos tem me ajudado muito. MUITO OBRIGADO
I am very happy to hear you like these videos and that you are learning Apps Script. 😀
You are just TOO good :D Every question I have along my journey you have already addressed
Thank you so much, best of wishes
Happy to hear you are benefiting from our videos! Maybe you could share them with your social media followers? 😀
Thank you and thanks for the comparison video as well!
Happy you liked it! 😀
for loop vs for each.. you doing a great job.. much appreciated..
Support from India 😎
Hi Chinmay! Thanks for the feedback! 😀
Here's the video where I compare for vs. foreach loop: ruclips.net/video/6Lu3HEuMOdE/видео.html
Thanks, I am looking forward to make automation where I will copy n paste huge data and when i press a button, data will be sent row by row as a POST request to an API endpoint.
Would love to know your views about this.
Por favor, mostre a diferença dos dois. Obrigado
I will do so. Obrigada, Chanel
Suppose we have some fields on google forms and we want to go through each field throgj app for forms. Suppose we have name and email id. We want to send email throgh forms submit. How we can do through google app script
for loops vs for each :)
Hi Kamille. Your wish is my command: ruclips.net/video/6Lu3HEuMOdE/видео.html
Happy learning and coding! 😀
for loop vs for each !
Hi Fabyo. Here is the video you requested: ruclips.net/video/6Lu3HEuMOdE/видео.html
Enjoy! 😀
Hi Chanel I'm new to app scripts here,
can't the forEach function just edit the "values" array where you retrieved the table data from the getValues() and use the [values] to range.setValue([values])
No, you shouldn't do that. The values you got in one go have to be changed and then you set the new values in the sheet. This is a batch operation.
Otherwise you would have to get the values cell by cell, change them and write them directly back to the sheet. Your script would take forever to run and would most likely stop because of a timeout.
HI. I think I'm more confused about this function. I think you explained some things in greater detail than I know about the loop. I don't understand about the function part of the loop and especially the singular and plural thing. I thought the name 'value' was unrelated to the variable 'values' in script. Hope I have relayed my confusion sufficiently. I will search your other videos. Thanks
OK, so i didn't realise before i think you were running the function inside the '(callback)' section rather than have a separate function where you pass the argument to.... did i get that right or am i still lost? thanks
Maybe have a look at this explanation of how the forEach method works: www.w3schools.com/jsref/jsref_foreach.asp
I came accross a couple of your tutorials and found them very helpful in AppsScript. I'm now facing a problem with looping (or repeating) the same scrips in every sheet in my spreadsheet. Here's the code that I'm dealing with down below. Both codes are valid, but the one which is supposed to work within all the sheets doesn't really work at all. Could you please advise how to solve this?
function hideRow1()
{
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("TEMPLATE_1");
var dropDown = sheet.getRange('B34:C34').getValue();
if(dropDown == "alphaJET")
{
sheet.showRows(37,75);
sheet.showRows(203,6);
}
else if(dropDown == "laserSYSTEM")
{
sheet.showRows(37,75);
sheet.showRows(203,6);
}
else
{
}
}
function alltabs(){
var spreadsheet = SpreadsheetApp.getActive();
var allsheets = spreadsheet.getSheets();
allsheets.forEach(function(sheet){
if(sheet.getSheetName() !=="Arkusz4"){
sheet.activate();
hideRow1();
}
})
}
Are are directly accessing sheets by very specific names like "Arkusz4". Do you have sheets by that name in all the files you want to use this script?
Hey - for the statement: Logger.log(`Here......${value}`); we need to enclose the string in backticks - " ` ", correct?
Yes when using template literals, to output the value of a variable together with a string, you need to use backticks. If you use single quotation marks it won‘t work.
how to store data output from foreach loop in variable??
Create an array outside of the loop and then have the items stored to that array on every iteration of the loop.
@@saperis Thanks for the reply.
I create an array outside of the loop, but I get an answer as only 1 item. I want all items to be in the array.
i need a script for there is one column which is some numbers write like 2 ,3,4,5 but condition is if there is mention number of 4 so till next five column print number till 5 (like 1,1,1,1,1) it is possible so give me the script
Thank you for watching this video. We don't offer writing scripts for others.
Hi, thanks for the video. I'm new in Java and I'm having some issues. Every month I should analyze a file with multiple and randon amount of sheets(with different names) with a macro. Here's the thing: I found out that work with their indexes would be easier, however, when I did a for lace just to run the script for all sheet's index and when I also tryed to work all the sheets by using .forEach(function(sheet)), it didn't run the code. It shows a error saying that .getRange() is not a function(I needed to use a letter of the alphabet inside the ''getRange(lletter, number)'', to implement the rows from another loop). I think I'm getting confuse with Java and Python. Plus I didn't found the documentation that says if there is a way to test/get the amount of sheets. Do you have any clue of where can I get this documentation?
Thanks for your comment. Just to clarify: Google Apps Script is essentially JavaScript. Java is another programming language and has nothing to do with Google Apps Script.
If you want to find out how to use Google Apps Script then check this official documentation: developers.google.com/apps-script
the getRange() method get's a specific range within a spreadsheet or a sheet. You can use this method by passing in the row and columns or A1 notation. Please check the reference to find out how to use this method: developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer)
@@saperis thank you very much! I'll study it all. Nice content in all videos. It's helping me a lot, tks :)
Hi Chanel :) Why a DO WHILE loop contained within a function (that I want run at the end of my script), completely stops all the previous functions? Is it possible?? Thanks
I don't know why your code is not working. In general a do...while loop will run at least once and until the condition evaluates to FALSE.
Here some more information on this type of loop: developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/do...while
@@saperis Ok.
Imagine function ABBOTT() that writes “START” on a spreadSheet and then calls a function COSTELLO().
The function COSTELLO() does a DO… WHILE loop that writes “STOP” at a certain time (with new Date() object): it will run the loop until it reach the scheduled time.
My problem is that function ABBOTT() writes “START” only when function COSTELLO() finishes the DO…WHILE loop.
Why this?? (thanks)
Apps Script is executed line by line. So if you first have "Start" written to the spreadsheet and then call the COSTELLO function, that's the order it should run in.
But I think you need someone to look at your exact code to pin point where the error is happening.
I don't offer any code reviews or development as a matter of fact. Check here for a freelancer that can help you with your script: www.upwork.com/hire/google-spreadsheet-freelancers/
@@saperis ok. thank you very much for your kindness :)