Google Sheets - Import Data from Another Sheet - Tutorial Part 1

[@NJHeart2Heart]

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.

[@arielmora2350]

Well presented, well illustrated. I like the approach of starting vanilla and then "What could go wrong?"

[@rhionysmael135]

This is so helpful and explanation is comprehensive, so easy to understand and follow.

[@lluiszardoya]

I love your tutorials man. They are so incredibly helpful!

[@FaizanKhan-yq1kr]

❤️❤️❤️❤️ thanks alot keep sharing updated stufff

[@BrayannMota]

Genius. I appreciate the video! Literally just saved me hours.

[@camilaliege1]

Incredible. Very well explicatory and easy to understand for a newbie like me. Thank you so much

[@arunarajeev2751]

Very well explained, solved one of my major issues . Thanks you so much

[@maryamahmad1604]

Phenomenal. You resolved my problem so quickly and easily. Great instructor. Thank you!

[@ExcelGoogleSheets]

Great to hear!

[@Anzeljaeg]

As always, you just make my day, thank you so much

[@ExcelGoogleSheets]

😀

[@alvarogs92]

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

[@imtristan]

Perfect!!!!!!! Am so happy I got new things from this video.. worth to a thumbs up!!!!

[@ExcelGoogleSheets]

Awesome! Thank you!

[@caseylavalle7742]

Is there a way for the import range function to auto update when data is changed on the originating sheet?

[@brand-image335]

Thank you for uploading, very helpful.

[@davidcastaldo7970]

Thank you for this! Incredibly clear and easy to follow

[@elmohandesphysics386]

Thank you a lot .. its easy to go with steps 👍👍

[@MarkGarrettBusinessCoach]

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!

[@sivakumarjagadhesan7404]

Thank You Sir, the video was very helpful.

[@thomasentena2678]

This is super awesome man! Appreciate your help! :)

[@ExcelGoogleSheets]

Any time!

[@jascrandom9855]

Holy fuck this would saved me SO MUCH PAIN back in the Day!

[@eadvm]

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?

[@itsAlabi]

Thank you, it works perfectly.

[@scottsager440]

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?

[@ytonaona]

Excellent tutorial. Thank you very much!

[@AriesAndPisces]

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

[@markuswinter-cdps3008]

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]

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.

[@markuswinter-cdps3008]

@@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]

I'm also trying to do this

[@markuswinter-cdps3008]

@@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.

[@caswellthompson6432]

@@markuswinter-cdps3008 thank you!

[@johnconstantin8751]

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 "

[@7KDreamerPlays]

what happens if the sheet needs to be used offline? will the link/data still appear?

[@ratheesh.r.svellanad2875]

Super well explained

[@algimantaskvecys5157]

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?

[@moonwalker14122]

did you figure it out ? please help

[@yomsha]

Thanks a lot

[@eddiepersa1752]

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 !!!!

[@yongyu2032]

Hi, how can i import data by using google script instead of the sheet?

[@michellpalacios2377]

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?

[@ExcelGoogleSheets]

This is probably what you're looking for kzbin.info/www/bejne/gIK2nZWHmqqtZ80

[@peterwoods35]

Good video! Couldnt named ranges simplify this process?

[@marjiebendian4080]

very informative. Thank you very much!

[@ExcelGoogleSheets]

😀

[@rambojacksonjr7887]

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

[@phigrecon]

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?

[@HoneyLaBronx]

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)

[@ExcelGoogleSheets]

kzbin.info/www/bejne/p2nEqIGJjrOXp5Y

[@Willplessis]

Is it possible to copy cells from one sheet to another sheet based on a drop down list into specific cells

[@yamilinsaurralde5101]

INTERESTING! Question... Is it possible to auto update each new row from Sheet A to Sheet B?

[@ExcelGoogleSheets]

Isn't that what this formula does?

[@mohitjangra6374]

Hello sir. Can we get data from one sheet to another sheet some selected cells

[@bharanidharan285]

Hi, Thanks, for the vidoe, i want import data one sheet to another with the sheet name base can you help me

[@taekilkim]

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!

[@HassanAlmaateeq]

can we import data from Google spreadshet to a document (google DOC) ?

[@Pradeepkankarwal]

wow this is how i integrate my sheets :)

[@davidmmmc]

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!

[@sushilprajapati3080]

How can I import data from the other sheet, data + chart are there?

[@danielvelasquez1851]

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

[@safanisacattleyaapriani9772]

Very helpful 👍

[@dreads2081]

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.

[@RaeGrafixDesigns]

Perfect! Thanks a million!

[@ExcelGoogleSheets]

👍

[@ScampMichael]

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?

[@ExcelGoogleSheets]

I haven't tested this, but if there is you would simply type that name in quotes as the second argument of this function.

[@ParthuXYZ]

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?

[@chandrashekharprajapati9451]

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.

[@ExcelGoogleSheets]

It's here kzbin.info/www/bejne/p6TUm4iud8iad9U

[@nhtp342501]

What if i just want to link only certain cells to another sheet?

[@karensonobe5356]

I tried to copy a sheet and when I click allow access, it sits on Adding permissions... HELP!

[@bartholomews9470]

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

[@karensonobe5356]

@@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...….

[@karensonobe5356]

@@bartholomews9470 Actually, I just left clicked and duplicated the sheet, it worked fine. Thanks for replying!! :)

[@russellcunningham6484]

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?

[@ExcelGoogleSheets]

Yes, but it's complicated. Can't really explain how in a comment.

[@pleabargain]

Very helpful! I subbed!

[@maitaki3497]

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?

[@ExcelGoogleSheets]

kzbin.info/www/bejne/p2nEqIGJjrOXp5Y

[@urbanbassman1]

Brilliant and inspirational!

[@opd9-28]

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!

[@ghinaspace876]

same.. i just changed it manually though lol

[@ExcelGoogleSheets]

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.

[@emaddawoud8487]

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

[@michaelskinnerjr9291]

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

[@johna1750]

VLOOKUP

[@giovannelamprea5353]

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! :)

[@nkwhph]

what if u only want to import/sync one cell?

[@HOFRodie]

does it work from on user to deferent user of gsheet?

[@ExcelGoogleSheets]

Yes.

[@rishadahmad437]

Excellent but i am getting an issue, I also want to import formatting..How to do this..

[@christyabraham1443]

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

[@parveshpatel2859]

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

[@MaxXxipanda]

Great Video, thank you! :)

[@ezhilezhil4317]

Sir, how can import tables from one sheet to another please explain.

[@reinaldohandaya]

somehow i stuck on adding permission when i allow access. Do you know how to solve this ?

[@williamnjau3987]

Very helpful! Thanks

[@steffen5121]

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?

[@RBMndz]

Hi Im getting Error! Result too large when I tried importing data any help ? any recommendation ?

[@superjaw]

Split ure range of row,, Example sheet1!A1:D10000 Than colab using {sheet1!A10001:D20000}

[@sajmosajmon5774]

How to import data by function for example importxml and SAVE data automatically?

[@kaivay16]

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

[@James-nv1wf]

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.

[@ballyakbar9298]

hi how if i want to combine with checkboxes

[@cressidamadeleindimapilis8862]

Is it automatic updating???

[@syahirahsalimsaat]

How can I import data from the same sheet but different tab though?

[@ExcelGoogleSheets]

What exactly are you trying to import?

[@vincentkwok5716]

Go to 5:01. You add the tab name in single quotes followed by an '!' before the range. "'2017'!A1:G13"

[@udhayasankar2459]

TAmil languages ley teech pannuinga na romba easy ya irukum sir

[@siiya_j]

why i cant get the allow acess button?

[@md.farukhossain2393]

Thanks, for the video, Thank u so Much

[@intissarchneb6789]

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!

[@urbanbassman1]

Is it your sheet?

[@amintharani3905]

Very well explicatory

[@julia.vasilieva]

Thank you) How this can be done with Google Apps script?

[@yongyu2032]

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.

[@gemignacio6267]

How to give access on other spreadsheet?

[@faryalwahab1878]

The ref error pop up is not showing me this blue button for allow access. What am i doing wrong?

[@ExcelGoogleSheets]

Make sure you are logged in and use the right account in both spreadsheets.

[@giovannelamprea5353]

...or, how can I transfer or get out the 'comment/s' to another cell? Is it possible to do that?

[@ExcelGoogleSheets]

QUERY function will not include comments.

[@Fernitano]

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!

[@matiasarielseoane5970]

HELP PLEASE! I´ve folloewed several tutorials already, but I only get #ERROR (no #REF) I´m sure it´s just a dumb typo but I can´t figure ir out =IMPORTRANGE("1x2MtSTncZkgc-e5yA_rr04PVRUcvEqiE2XS460efy48","'InsumosComunes'!E7")

[@matiasarielseoane5970]

Could it have something to do with GSuite? I tried the same on my personal, basic account and it worked just fine

[@ExcelGoogleSheets]

It seems like it might be a permission issue.

[@Nick_Papa]

awesome work

[@janetongera5856]

Helped alot. thanks

[@iswadi-edukasidanhobbi7347]

Thank you

[@mounirhiouani6215]

i would like tp thank you veeery mush siir

[@mdalifmoondol]

How to ms excel sheet to another Google sheet update

[@HealthandBeautyAP]

I am not an excel person. I am wondering why not just copy and paste the information?

[@ExcelGoogleSheets]

So that it automatically updates when source data updates.

[@paulloup5210]

excellent video !