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
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 "
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?
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?
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
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 !!!!
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?
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?
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 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!
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
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 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 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?
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)
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.
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...….
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?
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!
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?
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
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.
Well presented, well illustrated. I like the approach of starting vanilla and then "What could go wrong?"
Incredible. Very well explicatory and easy to understand for a newbie like me. Thank you so much
I love your tutorials man. They are so incredibly helpful!
This is so helpful and explanation is comprehensive, so easy to understand and follow.
Genius. I appreciate the video! Literally just saved me hours.
Phenomenal. You resolved my problem so quickly and easily. Great instructor. Thank you!
Great to hear!
❤️❤️❤️❤️ thanks alot keep sharing updated stufff
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
Very well explained, solved one of my major issues . Thanks you so much
Thank you for uploading, very helpful.
Perfect!!!!!!! Am so happy I got new things from this video.. worth to a thumbs up!!!!
Awesome! Thank you!
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
"
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?
Super well explained
Thank you for this! Incredibly clear and easy to follow
Thank you a lot .. its easy to go with steps 👍👍
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?
As always, you just make my day, thank you so much
😀
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
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 !!!!
Thank You Sir, the video was very helpful.
Is there a way for the import range function to auto update when data is changed on the originating sheet?
Holy fuck this would saved me SO MUCH PAIN back in the Day!
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
Good video! Couldnt named ranges simplify this process?
Excellent tutorial. Thank you very much!
Perfect! Thanks a million!
👍
Thank you, it works perfectly.
This is super awesome man! Appreciate your help! :)
Any time!
very informative. Thank you very much!
😀
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?
This is probably what you're looking for ua-cam.com/video/JQSlbQeEz1k/v-deo.html
INTERESTING! Question... Is it possible to auto update each new row from Sheet A to Sheet B?
Isn't that what this formula does?
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!
Thanks a lot
Very helpful 👍
Hi, how can i import data by using google script instead of the sheet?
Brilliant and inspirational!
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
Hi,
Thanks, for the vidoe, i want import data one sheet to another with the sheet name base can you help me
Very well explicatory
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!
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
Thank you!
awesome work
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 happens if the sheet needs to be used offline? will the link/data still appear?
THANK YOU!!!!!!!!
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 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.
wow this is how i integrate my sheets :)
can we import data from Google spreadshet to a document (google DOC) ?
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?
Thanks, for the video, Thank u so Much
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?
Is it possible to copy cells from one sheet to another sheet based on a drop down list into specific cells
Excellent but i am getting an issue, I also want to import formatting..How to do this..
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! :)
Very helpful! I subbed!
Helped alot. thanks
Thinks
Wow, I'm really surprised you simply just can't "share" a page to another. Wild.
For those who struggle. Try putting ; instead of comma.
excellent video !
Great Video, thank you! :)
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)
ua-cam.com/video/q8awNSYNdq4/v-deo.html
So nice. Thanks
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 ua-cam.com/video/qsqjUxBcgAs/v-deo.html
Hello sir.
Can we get data from one sheet to another sheet some selected cells
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
Sir, how can import tables from one sheet to another please explain.
What if i just want to link only certain cells to another sheet?
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!! :)
Thanks!!
well done! thank you!
Really great but different header how to fetch like
TAmil languages ley teech pannuinga na romba easy ya irukum sir
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?
ua-cam.com/video/q8awNSYNdq4/v-deo.html
How to import data by function for example importxml and SAVE data automatically?
Thanks!
Thank You!
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!
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?
i would like tp thank you veeery mush siir
what if u only want to import/sync one cell?
Sweet
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.
somehow i stuck on adding permission when i allow access. Do you know how to solve this ?
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
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
THANK YOU SO MUCH!
How to give access on other spreadsheet?
Thank u so much sir
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
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.
why i cant get the allow acess button?
How can I import data from the other sheet, data + chart are there?
does it work from on user to deferent user of gsheet?
Yes.