What makes this so useful is that you go through the simplest formula, then go over issues with the "easy way" and explain how to make the formula better.
Hi! First of all, thank you for this video! Following your example, how can I import the specific sales for a matching Sales_rep? For instance, if I have a larger list of sales_rep in my deestination worksheet but I only want to import the specific sales for those who match my criteria in the origin's sheet, is that possible? Thank you
Thank you so much for this information: Question for you. What if I wanted to pull multiple items that are in 1 column to another the tab or sheets? For example if I wanted to pull the data for Nike and New Balance?
Thanks for this tut as always !!! I have a question: Is there an app for android that connects to a google sheet to save data from the cell phone ? thanks !!!!
I have done just as you state I get an ERROR message, not a REF# message your URL is different than mine I see is that why? it states on the #ERROR!? ugh #ERROR! Formula parse Error, can you assist with this my spreadsheet is set up different than yours
This is such a great video! Thank you for the level of detail you show in building up the formula. Amazing stuff. I am trying to solve two problems: Background - I have an order detail sheet that is appended every hour and I have the Date:Time field when it was appended in ISO format 2022-01-20T20:01:57Z What I need to do is group and sum order data of the last update. The first part I have successfully done but, I am unable to extract ONLY the most recent update. The second issue, I am running into 1,000,000 cells when I make these summary tables in my original API export data table. Is it best to use the ImportRange formula to extract the data into a new sheet and then play with it there? Happy to hire you if this is a bigger question than I have the ability to answer in the chat. Thanks for your videos!
Thank you! Very well done! One noob question; if I were to import the data to a new sheet, would it cause any issues deleting the sheet that the data originated from?
Briliant tutorial. One question. How to import cell formatting (numbering, aligment, colors, etc.) in the same way between sheets / sheets documents? Learn Google Spreadsheets ? I have table with some info and it's formatted in a way to look pleasing, but formatting "mirror" table in different sheet is double job. Culd You help me in this case?
As always appreciate the videos. I've used importrange quite a bit. What I'm looking to do is to have a master sheet to which data gets imported from newly created reports automatically as the reports are created. The reports are not tabs within the Google sheet where the master is housed, rather each report is its own Google Sheet document. The master sheet contains a column which has a list of the report names (E.G. -Report2021-01, Report2021-02, Report2021-03, etc.). The reports are generated as needed, meaning for a calendar year there might be 200, there might be 300. Is there a way to automatically import the needed data without needing to go in and write an importrange formula (to obtain the newly create report's unique ID) every time a new report is created? Instead, have a function written that searches for the unique report name (in google drive folder), find the requisite tab each unique report, and import the data? Note: the data imported is from the same cell range in each report. Again, thanks for your time, and keep the amazing videos coming!!
@@ExcelGoogleSheets Thank you for the quick response!! Indeed, the data only needs to be a copy, so then it should work. Do any of your videos address how to do this? Or are there a combination of videos that would explain the steps needed? Thanks!
@@caswellthompson6432 Here's a code that I found that works: //Set folder ID var folderId = 'YOUR FOLDER ID FROM YOUR GOOGLE DRIVE HERE';
/*// Main function 1: List all folders, & write into the current sheet. function listFolders(){ getFolderTree(folderId, false); }; */ // Main function 2: List all files & folders, & write into the current sheet. function listAll(){ getFolderTree(folderId, true); }; // Get Folder Tree function getFolderTree(folderId, listAll) { try { // Get folder by id var parentFolder = DriveApp.getFolderById(folderId);
// Initialise the sheet var file, data, sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YOUR SHEET NAME HERE'); sheet.clear(); sheet.appendRow(["Full Path", "Name", "Date", "URL", "Last Updated", "Description", "Size"]);
// Get files and folders getChildFolders(parentFolder.getName(), parentFolder, data, sheet, listAll);
} catch (e) { Logger.log(e.toString()); } }; // Get the list of files and folders and their metadata in recursive mode function getChildFolders(parentName, parent, data, sheet, listAll) { var childFolders = parent.getFolders();
// List folders inside the folder while (childFolders.hasNext()) { var childFolder = childFolders.next(); // Logger.log("Folder Name: " + childFolder.getName()); data = [ parentName + "/" + childFolder.getName(), childFolder.getName(), childFolder.getDateCreated(), childFolder.getUrl(), childFolder.getLastUpdated(), childFolder.getDescription(), childFolder.getSize() ]; // Write sheet.appendRow(data);
// List files inside the folder var files = childFolder.getFiles(); while (listAll & files.hasNext()) { var childFile = files.next(); // Logger.log("File Name: " + childFile.getName()); data = [ parentName + "/" + childFolder.getName() + "/" + childFile.getName(), childFile.getName(), childFile.getDateCreated(), childFile.getUrl(), childFile.getLastUpdated(), childFile.getDescription(), childFile.getSize() ]; // Write sheet.appendRow(data); }
// Recursive call of the subfolder getChildFolders(parentName + "/" + childFolder.getName(), childFolder, data, sheet, listAll); } }; As you can see, I commented out the listFolders function (as I don't use it). The one thing that I noticed with this is that it reruns and repopulates information that is already there. For my purposes, this fills a tracking log with key data elements pulled from the generated reports. The more reports that are generated, the longer this script takes to run. Once I get up to 250-300 reports, it takes 10-15 minutes. What I have to figure out is rather than completely re-pulling every report, to just pull the newest additions (leaving the old ones as they are). That would cut down the run time of the script significantly. As is, it works. Just not as efficiently as I'd like.
I have a problem with format when pasting values from a table on a web page into a sheet. the numbers are somehow formatted as durations instead od numbers. if i try to convert them back to numbers the values are totally different. is there a way of selecting the format before pasting values into a sheet?
I have a feeling that you may be the only one who can answer this question, how to make Importrange formula to overwrite cells, Ex, if ImportRange function pulls 10 rows of data (A1:C10) but you have a formula in A2 then you will get an error "Array result was not expanded because will overwrite data in cell A2", thanks and you have a great, useful content "
AWESOME!! What if I want it by Days? if i want it to reflect all the values of the entire row into a different tab, EXAMPLE: if the value in a cell in tab 1 is Saturday then it reflects all the row of Saturday into tab 2 PLEASE HELP
Sir ! My question is related to google sheets. I have 4 users and they all have separate sheet to work with. And the data of all 4 sheets should be saved in a single master sheet. But no user could edit the data in the master sheet.
Haven't seen the whole video yet but I'm wondering - what if I want to do this from within the same spreadsheet? I'd like to make ONE tab that compiles ALL the data from the other tabs (each of those tabs represents a year - I'd like to make a Master tab that has all of them in one place)
good video. i cannot see "allow access" when i use importrange function, instead its showing "lear more" and when clicking it goes to importrange guidance page. Can you help to fix this
I enjoy taking in your videos. Wish I were still living in Chicago to take part in some live events. Any future plans to take these day courses and make them live and interactive so people from outside of the city could take part in learning from you?
I have a spreadsheet where I am importing data to another sheet using a query, but I am having and issue and can't figure out the answer. The spreadsheet has checkboxes using data validation. When I do the transfer, using the query, it brings the information over as True and False rather than the checkboxes. How do I fix this? Thank you for your help. I love your videos!
I thought you had a video that explains how to have a sheet import a row and add below automatically everything you make a change on another sheet using "AppendRow" function, but I can't find it now!
Help. It doesn't work for me on android. Instead of asking me to connect the sheets, it only says #ref! and nothing more. I tried everything, I could, but it didn't work. Any suggestions?
Ey! Great video! I tried but appears TypeError: Cannot read property 'getRange' of null at myFunction(Código:2:29) Could you help me? I´m would like to bring btc value, with importdata from Binance API. Thanks!
Thank you. Could you please lead me to the video that explains how to get all the (example) Nike rows (entire row) in all the different sheets in one sheet?
How do you add a search function into a cell? I want to have a cell that I can enter a work order number that then pulls data from a form response. I have watched videos on here for days and still have not come up with any solutions
if the file was originally a excel spreadsheet, its an easy fix, go to both of your files in your google drive, and right click them and convert both to google sheet file. None of your data will screw up. But you will just need to rewrite your importrange formula, with the new google sheet URL
@@bartholomews9470 Hello, I created a new sheet on the same spreadsheet, I want to copy from another sheet on the same google doc. I'm still getting adding permissions...….
3 step 1. ss.getDataRange().clearContent(); 2.var datas= targetSheet.getDataRange().getValues(); 3.ss.getRange(1,1,datas.length,datas[0].length).setValues(datas). that is what i did on my own, hope it help.
What makes this so useful is that you go through the simplest formula, then go over issues with the "easy way" and explain how to make the formula better.
This is so helpful and explanation is comprehensive, so easy to understand and follow.
Well presented, well illustrated. I like the approach of starting vanilla and then "What could go wrong?"
❤️❤️❤️❤️ thanks alot keep sharing updated stufff
Genius. I appreciate the video! Literally just saved me hours.
I love your tutorials man. They are so incredibly helpful!
Incredible. Very well explicatory and easy to understand for a newbie like me. Thank you so much
Super well explained
Very well explained, solved one of my major issues . Thanks you so much
Perfect!!!!!!! Am so happy I got new things from this video.. worth to a thumbs up!!!!
Awesome! Thank you!
As always, you just make my day, thank you so much
😀
Phenomenal. You resolved my problem so quickly and easily. Great instructor. Thank you!
Great to hear!
Is there a way for the import range function to auto update when data is changed on the originating sheet?
Hi! First of all, thank you for this video! Following your example, how can I import the specific sales for a matching Sales_rep? For instance, if I have a larger list of sales_rep in my deestination worksheet but I only want to import the specific sales for those who match my criteria in the origin's sheet, is that possible? Thank you
Thank you, it works perfectly.
Thank you for uploading, very helpful.
Thank You Sir, the video was very helpful.
Thank you so much for this information: Question for you. What if I wanted to pull multiple items that are in 1 column to another the tab or sheets? For example if I wanted to pull the data for Nike and New Balance?
This is probably what you're looking for th-cam.com/video/JQSlbQeEz1k/w-d-xo.html
Excellent tutorial. Thank you very much!
Thank you for this! Incredibly clear and easy to follow
Thank you a lot .. its easy to go with steps 👍👍
Very helpful 👍
Thanks for this tut as always !!! I have a question: Is there an app for android that connects to a google sheet to save data from the cell phone ? thanks !!!!
I have done just as you state I get an ERROR message, not a REF# message your URL is different than mine I see is that why? it states on the #ERROR!? ugh #ERROR! Formula parse Error, can you assist with this my spreadsheet is set up different than yours
very informative. Thank you very much!
😀
Good video! Couldnt named ranges simplify this process?
Perfect! Thanks a million!
👍
Is it possible to copy cells from one sheet to another sheet based on a drop down list into specific cells
This is super awesome man! Appreciate your help! :)
Any time!
This is such a great video! Thank you for the level of detail you show in building up the formula. Amazing stuff.
I am trying to solve two problems:
Background - I have an order detail sheet that is appended every hour and I have the Date:Time field when it was appended in ISO format 2022-01-20T20:01:57Z
What I need to do is group and sum order data of the last update. The first part I have successfully done but, I am unable to extract ONLY the most recent update.
The second issue, I am running into 1,000,000 cells when I make these summary tables in my original API export data table. Is it best to use the ImportRange formula to extract the data into a new sheet and then play with it there?
Happy to hire you if this is a bigger question than I have the ability to answer in the chat.
Thanks for your videos!
Thank you! Very well done! One noob question; if I were to import the data to a new sheet, would it cause any issues deleting the sheet that the data originated from?
can we import data from Google spreadshet to a document (google DOC) ?
Briliant tutorial. One question. How to import cell formatting (numbering, aligment, colors, etc.) in the same way between sheets / sheets documents? Learn Google Spreadsheets ? I have table with some info and it's formatted in a way to look pleasing, but formatting "mirror" table in different sheet is double job. Culd You help me in this case?
did you figure it out ? please help
what happens if the sheet needs to be used offline? will the link/data still appear?
Is there a way to import a sheet by url input given by the user from a script ?, I mean doing a redirect by url
Hi Thanks for the video, could it be possible to include the files you use for the videos as links to we could practice? Thank you!
As always appreciate the videos. I've used importrange quite a bit. What I'm looking to do is to have a master sheet to which data gets imported from newly created reports automatically as the reports are created. The reports are not tabs within the Google sheet where the master is housed, rather each report is its own Google Sheet document. The master sheet contains a column which has a list of the report names (E.G. -Report2021-01, Report2021-02, Report2021-03, etc.). The reports are generated as needed, meaning for a calendar year there might be 200, there might be 300. Is there a way to automatically import the needed data without needing to go in and write an importrange formula (to obtain the newly create report's unique ID) every time a new report is created? Instead, have a function written that searches for the unique report name (in google drive folder), find the requisite tab each unique report, and import the data? Note: the data imported is from the same cell range in each report. Again, thanks for your time, and keep the amazing videos coming!!
Depends.
If the data in the master should just be a copy then yes.
If the data in the master is supposed to be a linked formula then no.
@@ExcelGoogleSheets Thank you for the quick response!! Indeed, the data only needs to be a copy, so then it should work. Do any of your videos address how to do this? Or are there a combination of videos that would explain the steps needed? Thanks!
I'm also trying to do this
@@caswellthompson6432 Here's a code that I found that works:
//Set folder ID
var folderId = 'YOUR FOLDER ID FROM YOUR GOOGLE DRIVE HERE';
/*// Main function 1: List all folders, & write into the current sheet.
function listFolders(){
getFolderTree(folderId, false);
};
*/
// Main function 2: List all files & folders, & write into the current sheet.
function listAll(){
getFolderTree(folderId, true);
};
// Get Folder Tree
function getFolderTree(folderId, listAll) {
try {
// Get folder by id
var parentFolder = DriveApp.getFolderById(folderId);
// Initialise the sheet
var file, data, sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YOUR SHEET NAME HERE');
sheet.clear();
sheet.appendRow(["Full Path", "Name", "Date", "URL", "Last Updated", "Description", "Size"]);
// Get files and folders
getChildFolders(parentFolder.getName(), parentFolder, data, sheet, listAll);
} catch (e) {
Logger.log(e.toString());
}
};
// Get the list of files and folders and their metadata in recursive mode
function getChildFolders(parentName, parent, data, sheet, listAll) {
var childFolders = parent.getFolders();
// List folders inside the folder
while (childFolders.hasNext()) {
var childFolder = childFolders.next();
// Logger.log("Folder Name: " + childFolder.getName());
data = [
parentName + "/" + childFolder.getName(),
childFolder.getName(),
childFolder.getDateCreated(),
childFolder.getUrl(),
childFolder.getLastUpdated(),
childFolder.getDescription(),
childFolder.getSize()
];
// Write
sheet.appendRow(data);
// List files inside the folder
var files = childFolder.getFiles();
while (listAll & files.hasNext()) {
var childFile = files.next();
// Logger.log("File Name: " + childFile.getName());
data = [
parentName + "/" + childFolder.getName() + "/" + childFile.getName(),
childFile.getName(),
childFile.getDateCreated(),
childFile.getUrl(),
childFile.getLastUpdated(),
childFile.getDescription(),
childFile.getSize()
];
// Write
sheet.appendRow(data);
}
// Recursive call of the subfolder
getChildFolders(parentName + "/" + childFolder.getName(), childFolder, data, sheet, listAll);
}
};
As you can see, I commented out the listFolders function (as I don't use it). The one thing that I noticed with this is that it reruns and repopulates information that is already there. For my purposes, this fills a tracking log with key data elements pulled from the generated reports. The more reports that are generated, the longer this script takes to run. Once I get up to 250-300 reports, it takes 10-15 minutes. What I have to figure out is rather than completely re-pulling every report, to just pull the newest additions (leaving the old ones as they are). That would cut down the run time of the script significantly. As is, it works. Just not as efficiently as I'd like.
@@markuswinter-cdps3008 thank you!
If there is a named range in the sheet that it's being imported from can that name be used in the range parameter of the import function?
I haven't tested this, but if there is you would simply type that name in quotes as the second argument of this function.
Hi, how can i import data by using google script instead of the sheet?
Hi,
Thanks, for the vidoe, i want import data one sheet to another with the sheet name base can you help me
Hello sir.
Can we get data from one sheet to another sheet some selected cells
INTERESTING! Question... Is it possible to auto update each new row from Sheet A to Sheet B?
Isn't that what this formula does?
Holy fuck this would saved me SO MUCH PAIN back in the Day!
Is there a video that explains how to link an entire sheet to a certain cell? Almost like an web link ? Or can you only move data
I have a problem with format when pasting values from a table on a web page into a sheet. the numbers are somehow formatted as durations instead od numbers. if i try to convert them back to numbers the values are totally different. is there a way of selecting the format before pasting values into a sheet?
I publish my google sheet, but in pdf format it's always show in lendscape but I want to change in to portrait how do i do?
does it work from on user to deferent user of gsheet?
Yes.
I have a feeling that you may be the only one who can answer this question, how to make Importrange formula to overwrite cells, Ex, if ImportRange function pulls 10 rows of data (A1:C10) but you have a formula in A2 then you will get an error "Array result was not expanded because will overwrite data in cell A2", thanks and you have a great, useful content
"
AWESOME!! What if I want it by Days?
if i want it to reflect all the values of the entire row into a different tab, EXAMPLE: if the value in a cell in tab 1 is Saturday then it reflects all the row of Saturday into tab 2
PLEASE HELP
Sir ! My question is related to google sheets.
I have 4 users and they all have separate sheet to work with. And the data of all 4 sheets should be saved in a single master sheet. But no user could edit the data in the master sheet.
It's here th-cam.com/video/qsqjUxBcgAs/w-d-xo.html
Haven't seen the whole video yet but I'm wondering - what if I want to do this from within the same spreadsheet? I'd like to make ONE tab that compiles ALL the data from the other tabs (each of those tabs represents a year - I'd like to make a Master tab that has all of them in one place)
th-cam.com/video/q8awNSYNdq4/w-d-xo.html
If I want to import data for perticular Brand i.e. NIKE and also want to share same data with someone with viewing rights only. please
good video.
i cannot see "allow access" when i use importrange function, instead its showing "lear more" and when clicking it goes to importrange guidance page. Can you help to fix this
Hi thanks for the video, can anyone explain how to do this using the android app? I can't seem to find the sheet URL. TIA.
What if i just want to link only certain cells to another sheet?
I enjoy taking in your videos. Wish I were still living in Chicago to take part in some live events. Any future plans to take these day courses and make them live and interactive so people from outside of the city could take part in learning from you?
Thanks a lot
somehow i stuck on adding permission when i allow access. Do you know how to solve this ?
How to import data by function for example importxml and SAVE data automatically?
Very well explicatory
Sir, how can import tables from one sheet to another please explain.
I have a spreadsheet where I am importing data to another sheet using a query, but I am having and issue and can't figure out the answer. The spreadsheet has checkboxes using data validation. When I do the transfer, using the query, it brings the information over as True and False rather than the checkboxes. How do I fix this? Thank you for your help. I love your videos!
same.. i just changed it manually though lol
Checkbox is just data validation added to true/false cells, so you'll need to add the validation on the sheet where you did QUERY as well.
Thanks, for the video, Thank u so Much
Very helpful! Thanks
I thought you had a video that explains how to have a sheet import a row and add below automatically everything you make a change on another sheet using "AppendRow" function, but I can't find it now!
what if u only want to import/sync one cell?
Ho do I get over an error when one of the work sheet doesn't have any data yet? I get a #VALUE!. I haven't put any data yet in one of the sheets
How can I import data from the other sheet, data + chart are there?
Help. It doesn't work for me on android. Instead of asking me to connect the sheets, it only says #ref! and nothing more. I tried everything, I could, but it didn't work. Any suggestions?
Excellent but i am getting an issue, I also want to import formatting..How to do this..
Thank you
awesome work
Brilliant and inspirational!
Ey! Great video! I tried but appears TypeError: Cannot read property 'getRange' of null
at myFunction(Código:2:29)
Could you help me? I´m would like to bring btc value, with importdata from Binance API.
Thanks!
Thank you. Could you please lead me to the video that explains how to get all the (example) Nike rows (entire row) in all the different sheets in one sheet?
th-cam.com/video/q8awNSYNdq4/w-d-xo.html
Really great but different header how to fetch like
How do you add a search function into a cell? I want to have a cell that I can enter a work order number that then pulls data from a form response. I have watched videos on here for days and still have not come up with any solutions
VLOOKUP
Any way to get the SHEETNAME that the IMPORTRANGE came from on each record on the Master, so you know where each record came from?
Yes, but it's complicated. Can't really explain how in a comment.
wow this is how i integrate my sheets :)
The ref error pop up is not showing me this blue button for allow access. What am i doing wrong?
Make sure you are logged in and use the right account in both spreadsheets.
This is a big help but I have another problem, I can't include the 'comments' to another google sheet. Please help me how.. Thank you! :)
Helped alot. thanks
i would like tp thank you veeery mush siir
I tried to copy a sheet and when I click allow access, it sits on Adding permissions... HELP!
if the file was originally a excel spreadsheet, its an easy fix, go to both of your files in your google drive, and right click them and convert both to google sheet file. None of your data will screw up. But you will just need to rewrite your importrange formula, with the new google sheet URL
@@bartholomews9470 Hello, I created a new sheet on the same spreadsheet, I want to copy from another sheet on the same google doc. I'm still getting adding permissions...….
@@bartholomews9470 Actually, I just left clicked and duplicated the sheet, it worked fine. Thanks for replying!! :)
excellent video !
Is it automatic updating???
THANK YOU!!!!!!!!
How to give access on other spreadsheet?
Great Video, thank you! :)
Very helpful! I subbed!
hi how if i want to combine with checkboxes
So nice. Thanks
why i cant get the allow acess button?
Hi
Im getting Error! Result too large
when I tried importing data
any help ?
any recommendation ?
Split ure range of row,,
Example sheet1!A1:D10000
Than colab using {sheet1!A10001:D20000}
...or, how can I transfer or get out the 'comment/s' to another cell? Is it possible to do that?
QUERY function will not include comments.
Thanks!!
Hi, please why did it get too much time to import data (the table contain more then 100 line ), then it ends with no result!
Is it your sheet?
Thank you) How this can be done with Google Apps script?
3 step
1. ss.getDataRange().clearContent();
2.var datas= targetSheet.getDataRange().getValues();
3.ss.getRange(1,1,datas.length,datas[0].length).setValues(datas).
that is what i did on my own, hope it help.
How to copy fill color to another tab just text
I am not an excel person. I am wondering why not just copy and paste the information?
So that it automatically updates when source data updates.
well done! thank you!