ฝัง
- เผยแพร่เมื่อ 16 มิ.ย. 2024
- In this video you will learn four different methods to get a specific range in Sheets with Google Apps Script.
00:00 Intro
00:20 getDataRange() method
2:00 getRange(a1Notation) method
3:25 getRange()
6:01 getRangeByName(name) method
👉🏽 If you're interested in leveling up your Google Apps Script skill have a look at our online course: https//courses.saperis.io
#GoogleAppsScriptGetRange #GoogleSheets #GoogleAppsScript #AppsScript #GoogleWorkspace #saperis #ChanelGreco
-----
saperis - Helping you become a Google Workspace Pro!
Sign up to our TH-cam channel for more Google Workspace, Gmail, Google Apps Script, Google Drive, etc. videos: / saperis
![[Full Episode] MasterChef Junior Thailand มาสเตอร์เชฟ จูเนียร์ ประเทศไทย Season 3 Episode 3](http://i.ytimg.com/vi/A1XXYbV048k/mqdefault.jpg)
Exactly what I've been searching for. Comprehensive and clearly explained. You saved me a lot of time. Thank you so much
You're very welcome! 😃
Thank you very much for the video!! Very good and clear explanation!
Keep'em coming!!
☺
You are welcome! 😀
Thank you, for the help !
You're welcome! 😀
I am new to Google App Script. Your tutorials are very well structurated and worked best for me. Thank you for that.
Glad it helped! 😀
Gracias por tener un ingles tan claro. Lo entendí perfectamente. Y has sido muy didactica. Muy amable. Gracias!
Estoy feliz de que te haya gustado 😀
Excelente, saludos desde Chile
Gracias 😀
Thank you for the clear video. You really know how to teach!
How would you use getRange when the first two parameters should be the row and column of the currentActiveCell followed up bij numRows and numColumns?
If you want to access the current active cell you can use the getCurrentCell() method.
This gives you back a range.
The getRange() method also gives you back a range. Just that here you define the range by passing in the parameters of row, column, row count and column count.
You can't use the getCurrentCell() method together with the getRange() method.
@@saperis Thank you for the clear reply! ....kind regards Jan
Thank you for this. Is there a way we can get multiple ranges in app script say A1:A5 and C1 to C5 ?
Thanks for this tutorial. Very clear and concise. I was wondering is there a way to getRange() with a parameter you set in your function or by getting the current row or current cell? I'm having a hard time conceptualizing it this way.
To understand what parameters you can use, check the official Apps Script documentation. That's the easiest way to find out how to use methods: developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column
ty love
You're welcome 😀
Great..👍👍
Thanks ✌️
best video tutorial ever! please, createTextFinder tool for some videos in the future 🙏
Thanks for the feedback. I don't make any Apps Script videos on this channel anymore. See why: th-cam.com/video/OHd_xEabivg/w-d-xo.html
Hi, thank you for this video. I also need to get a hyperlink that is added on some part of the text in cells. I guess I need to use getActiveSpreadsheet function but I don't know how to do it.
With getAchtiveSpreadsheet you simply get the entire Google Sheets file that you currently have opened in your browser. Form there you need to use further methods to access the part of the data that you are interested in.
Very interesting! I'm trying to get a variable range in a coulmn, so I think i need to get the range to lastRow of that Column. Is it possibile without a loop?
I wouldn't use a loop to get data from a spreadsheet. I would much rather use the getDataRange() method. It gives you back all the data no matter how many rows it contains.
Thank you very much for the video .. what about blanks and formulas ?
Check the developer documentation to see how to retrieve this from a sheet: developers.google.com/apps-script/reference/spreadsheet
I love your tutorial.
Question: Is there a way to set the column size of a google sheet by using javascript?
I have been looking but could not find one. I want to use javascript, not the manual method.
The google sheet I created is by javascript (where I got most of the methods from you, thank you); I want to add the capability of sizing a range of columns.
Greatly appreciated.
I had a look at the Apps Script developer documentation. I don't think it's possible to set the width of a column using Apps Script.
developers.google.com/apps-script/reference/spreadsheet
Hello, can we export a certain range field as example a1:f15 jpeg.
I think that should be possible. Check this video to find out how to export a Sheets file: th-cam.com/video/xUovS4fwqsg/w-d-xo.html
Great video! I just have one question about getNameByRange. When I try to use it, the log says its not a function but it shows the pop up dialog box when I input an argument. I then tried getNamedRange(Name) but it says that the input of a string is wrong. All I'm trying to do is get the length index of a column through script. Any help would be greatly appreciated!
As you learned through the video there are multiple ways to get data from a sheet.
If you chose to use the getNamedRange method I'm guessing you have created a named range within your sheet. In that case the parameter passed into the method has to be the name of the range.
So if I named my range Person_and_Scores I have to use the method like so ON THE SPREADSHEET and not on the sheet:
getNamedRange('Person_and_Scores');
@@saperis Ah I see! Thanks!!
Great explanation, I have a question: what if for example I want to store a cell value as a variable; Sheet1 Cell B2 value Is stored as "Sales". What method should I apply? Thank you!!
To get the content of one specific cell you can use .getValue() on the range.
developers.google.com/apps-script/reference/spreadsheet/range#getvalue
@@saperis Thank you!
Excellent teacher with excellent lessons) Please, can you tell me why google script logger shows ranges in different ways? For example, in the video when you used a1Notation, all results appeared in one row (not in different rows for better presenting information as other examples). I have the same problem: when I use getRange() with all 4 parameters (4, 14, 1, 23) , console.log() show me 23 rows :)) How could I solve this problem?
The logger shows you the data just like Apps Script sees it. That's why it's not represented in columns but instead in one row. There is no way of changing that.
is it possible to get ranges starting from the class Range? or do you always have to start from the class Sheet?
You have to start from the class Sheet or Spreadsheet.
Hi how do i enable permissions. I went into the .json file and added the oauthScopes but it did not work
What for permissions are you trying to enable? In the video tutorial you can see that I simply click my way through the default permissions.
Please make a video for getting data from the rows which have true values..
Thank you for watching this video. I don't do any Apps Script videos anymore on this channel. But I invite you to check out the Google Workspace Developers TH-cam channel: www.youtube.com/@googleworkspacedevs
could u plz make a video on how to get active user using app script. I have seen that if the user is not the owner then getActiveuser() function is not working desirably
Thanks for watching. I don't do any Apps Script or Google Workspace videos anymore. Find out why in this video: th-cam.com/video/OHd_xEabivg/w-d-xo.html
Is there a way to get fixed selected cells from all sheets (except master sheet) to a master sheet?
For eg, If I have a sheet for each day of a month and I want to compile data from all sheets to a master sheet
That should be possible. We have a video on how to copy content from one sheet to another that might offer some ideas on how to accomplish your goal: th-cam.com/video/xmtwWKYLJHs/w-d-xo.html
@@saperis Thanks for the revert! It does to some extent, but I want to get data prospectively from all new sheets made to a master sheet, but the data should be added in a different row each time so that I have a compiled data at the end.
@UC%F0%9F%98%80zKzrFTs7_vZGA3lU0IDSbQ Ok, well we have no video on what you are exactly trying to do. But I'm sure you'll get it done.
Hi I can't see the option Script editor in google Sheets, how to fix that?
Google has updated the Google Sheets menu since this video was made. Check Extensions > Apps Script.
Assume that c column has email_list , How to send in e-mail body the selected data A1:B7
Well, you would have to write a separate script to actually send out the email. Our video tutorial here only demonstrates how to get data from a specific range within a Google Sheet.
Those examples didn't work form me. I need the lastrow from lastcolumn. But lastcolumn don't have all the rows with values. So the last column have less values than the other columns. I can't find the right method for this issue. But I will find it! ;o)
Maybe also check the method getLastColumn(): developers.google.com/apps-script/reference/spreadsheet/sheet#getlastcolumn
And also check getLastRow(): developers.google.com/apps-script/reference/spreadsheet/sheet#getlastrow
My google sheets in tools, don't show Script editor. Why?
Because Google updated the menu of Google Sheets since we've published this video. Go to 'Extensions' > 'Apps Script'.
@@saperis Thank's! Your videos are amazing! :)
Is it possible instead of A1:B3. You instead use a cell with A1:B3
I'm not sure if I understand your question. Yes, you can get any range you want and it doesn't have to be the same I used.
@@saperis
What I mean is... you used A1 notation as ( 'A1:B3') on your example. Is it possible to use a Cell value inside a sheet instead. Example G14 = A1:B3
So new A1 Notation would be range('G14') instead. When I tried it it didn't work.
@@DavenneChua The method expects an A1 notation and not a cell that contains A1 notations as its value. That's my assumption since both my tests and your tests have failed.
@@saperis i see... is there a way to change it as such? To Get range from a cell value then use it for getrange. Or is there simpler way. If none that's ok. Thanks for your replies. :)
@@saperis
function ABCDEF()
{
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('SHEETNAME'), true);
spreadsheet.getRange('C4:C49').setValue('ABCDEF');
}
if possible I just need C4:C49 to be from a cell G14 = C4:C49
Hello,
This line of code uses the id entered manually in the script
let copyFile = DriveApp.getFileById('1GhpGpHM......').makeCopy(),
copyID = copyFile.getId(),
copyDoc = DocumentApp.openById(copyID),
How could I use the ID that can be found manually written in the google spreadsheet, so that if I change it from the spreadsheet, it will also change automatically in the script. I have a list of IDs associated with some templates.
I used :
var ID_template = ss.getRangeByName('ID_template').getValue;
and then
let copyFile = DriveApp.getFileById(ID_template).makeCopy(),
copyID = copyFile.getId(),
copyDoc = DocumentApp.openById(copyID),
but without any result.
Can something be done about this?
The getRangeByName() method you are using is only for named ranges in a sheet. So that will never work the way you are using it.
Instead, get that file ID by referencing the exact cell like ss.getRange('A1') or whatever cell it is in.
Very important when using Apps Script: read the official documentation to find out what methods do. developers.google.com/apps-script/reference/spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Thông tin liên hệ (Câu trả lời)');
var emailTo = sheet.getRange(sheet.getLastRow(), 3).getValue();
I don't khow why there is an error "Cannot read property 'getRange' of null"(Sorry, i'm new)
I can't tell by looking at the code you shared here.
Maybe there is a typo?
@@saperis Thank you very much. I found the problem