--- ``` (MANDATORY INTRO ) ### Outro **ARIEL 1:** Heeey there! Yawnnnn Welcome back to EGreenNews! Ariel here, with my AI bestie Ariel, and today we’re diving into a super exciting topic: google sheets tricks for 2025. Buckle up for some mind-blowing insights! hey there today's video is a compilation 0:02 of three previous videos that I released 0:04 we're going through all five built-in 0:07 functions to import data into your 0:09 Google sheet I hope you find Value in 0:11 this please hit the like button and 0:13 subscribe to my channel for more content 0:15 like this now let's go can you connect IMPORTRANGE 0:19 two Google Sheets data to one another 0:22 yes and it's a built-in function that's 0:25 easy to use it's called import range you 0:28 need two things you need the URL of the 0:31 spreadsheet that you want to pull data 0:32 from and then you need the spreadsheet 0:35 range on that spreadsheet that you want 0:38 the data to come from 0:40 check it out 0:41 import range B2 this is a URL for the 0:45 data over here which is just a bunch of 0:47 Samsung Galaxy random data that I got on 0:51 kaggle that's the URL if you want to 0:53 type it out you need to put it in quotes 0:56 and type out HTTP blah blah blah I find 1:00 it easier and cleaner to just paste it 1:02 into a cell and then reference that cell 1:04 then also within quotes you need to 1:08 reference the range of the spreadsheet 1:10 you're pulling data from 1:12 I've renamed this sheet tab to data 1:15 that's why you see data exclamation mark 1:17 A1 through K and that will as I hit 1:20 enter pull every piece of data on the 1:23 Samsung Galaxy phones into my 1:26 spreadsheet 1:27 now you can minimize this to say the 1:30 first 10 rows if you want to just get 1:33 those first 10 rows any range that you 1:35 want to put in here what if you want to IMPORTHTML 1:38 pull data from a website itself well 1:41 there's an import HTML function also 1:45 built in this is going to take you 1:47 guessed it the URL a query which this is 1:51 going to be one of two words you're 1:52 going to either have the word list or 1:54 the word table and then an index meaning 1:57 the first table the second table like 2:00 which number table if there's more than 2:02 one on the page do you want to pull in 2:05 now what are list and index those are 2:10 HTML descriptions of the data on a page 2:14 if we go quickly to the article that I'm 2:17 referencing on Wikipedia here and we 2:19 pull up the inspection developer tools 2:22 we can see that this right here is 2:25 formatted as a table in HTML 2:29 in other cases you may come across a 2:32 list an unordered or an ordered list of 2:35 HTML data you want to bring in 2:37 that's getting into the weeds a little 2:38 bit let's just check out what our import 2:41 does here we are again referencing the 2:44 URL in this B3 cell we're typing table 2:48 because that's the table we want we're 2:49 going to get the zeroth table that's the 2:52 first one on the sheet and check it out 2:54 it is pulling over this table of 2:56 information and stats into our 2:59 spreadsheet let's import some more data IMPORTDATA 3:02 into our Google Sheets shall we today 3:04 let's look at import data and import 3:06 feed though all of these are built-in 3:08 functions two Google Sheets that you 3:10 have access to right now import data all 3:13 it wants is a website so I've found some 3:16 data sets over here and I've found this 3:19 in a CSV format it wants to see either 3:22 CSV format or tsv format that's comma 3:25 separated or tab separated values and 3:29 then I've pasted that in here for our 3:30 URL to use and all it wants is hey 3:34 import data URL there it is there's the 3:38 data it's doing the heavy lifting for us 3:40 if for some reason you hit a snag you 3:43 can always go in here and manually put 3:45 the delimiter as a comma in our case 3:47 here that's a CSV file that we are 3:50 pulling in but it shouldn't need that 3:52 something would have to be messed up for 3:54 you to have to manually type that in 3:57 that's it that's easy import data 3:59 nothing to it import feed is about as IMPORTFEED 4:02 simple it needs a URL for an RSS feed so 4:06 here's the RSS feed page for one of my 4:09 podcasts and here is the XML code from 4:12 that actual page it's just a huge long 4:15 document that keeps track of every 4:18 single episode and all the pieces of 4:19 information about a podcast 4:22 so with import feed we can come down 4:25 here and say import feed give it the URL 4:28 then everything else is optional but all 4:30 I wanted here was the title for each of 4:32 the items which gives me each episode 4:34 title and then I wanted the headers to 4:37 be there so I wanted it to say title at 4:39 the top and then I said give me 50 items 4:42 and I actually don't have 50 podcasts 4:44 but that just pulled in everything that 4:46 I do have I did the same thing over here 4:49 to pull in the URLs for each of those so 4:52 if I went over here then it would 4:54 actually take me to each individual 4:56 podcast player page and that's all there 4:59 is to it now if you wanted to get some 5:02 more information on some of those 5:04 options you can pull up the help box 5:06 here and it'll tell you about those 5:08 query options like the items and the URL 5:11 that I used what all you can do with IMPORTXML 5:14 those 5:15 all right let's talk about import XML 5:18 this is the last of the five of the 5:21 import functions that are built into 5:23 Google Sheets I've gone over the others 5:25 they're up here for your reference and 5:28 they will be included in the Google 5:29 sheet that I've Linked In the 5:31 description below today though we're 5:33 looking at a very powerful one but it's 5:36 also complicated import XML let's start 5:39 with a web page because we need a URL 5:41 address and here we have it up here this 5:43 is my author page at free codecamp it's 5:46 just got a list of the articles that 5:48 I've written the titles and the links to 5:50 them Etc if you pull up the developer 5:53 tools we can then inspect the page and 5:57 see some things about all of these 6:00 elements to use import XML you have to 6:03 know a little bit of HTML which is how 6:06 information is presented on a website 6:09 you can see that down here as I 6:12 highlight or as I scroll through 6:13 different items it's highlighting them 6:15 up there on the web page 6:17 for instance this H2 class postcard 6:20 title this is actually this section 6:23 right here the title to that article 6:26 as we'll find if we inspect others they 6:29 are all H2 class postcard titles and 6:33 then they simply have different text as 6:35 well as what's called an anchor tag with 6:37 an href that's the link to this 6:40 particular article okay so you can find 6:44 out tons of stuff just by right clicking 6:46 a page and saying inspect and then it 6:48 will pull up the developer tools down 6:51 below let's go into our spreadsheet 6:53 where I've pulled the address to that 6:55 page for us to use and it's in a11 6:59 and then let's start out by writing 7:01 something simple ourselves import XML 7:04 a11 which is where the URL of that page 7:07 is that's my author page and then XPath 7:10 query so this is going to take a query 7:14 that's written in double quotes with a 7:16 double slash at the start of it and we 7:18 can put in something as simple as H1 7:20 which is or an H2 rather which is a 7:23 header element and it will pull in all 7:25 of those titles because they're all H2 7:27 elements but it's also pulling this guy 7:30 right here this little snippet about me 7:32 I don't want that so in order to be a 7:37 little bit more specific over here in 7:39 our actual statement we're going to 7:42 write a11 again slash slash asterisk 7:45 that's gonna that's shorthand for saying 7:47 I want all of these things and the 7:50 things we want are enclosed in Brackets 7:52 we're going to go at class 7:55 postcard title okay that's going to say 7:58 hey look for anything with the class 8:00 postcard title and we found that in our 8:04 page right here okay so once we do that 8:07 it's going to pull in all of the titles 8:09 to my articles in order to get the slug 8:14 or the URL for the individual articles 8:16 we're going to do something similar it 8:18 starts out the same way so we're getting 8:20 that class postcard title and then we're 8:23 saying hey go one step further nested 8:26 beneath there is an anchor tag so slash 8:29 slash a slash at href 8:33 it gets a little funky right but it's 8:36 real powerful because you can get super 8:37 specific on what information you want to 8:40 pull into your Google sheet so that's 8:42 saying look at those H2 elements the 8:45 titles we just pulled and then attached 8:47 to those is this anchor tag with a URL 8:49 so bring that over here and sure enough 8:52 it brings that over here 8:53 now in order to turn that into a working 8:55 link I just concatenated the 8:58 freecodecamp.org part with that slug I 9:02 just extracted 9:03 okay this is the Bare Essentials of what 9:07 you can do but it gives you a good 9:08 introduction to the power of XPath query 9:13 uh here is a cheat sheet this will be 9:16 linked in the description also linked as 9:19 that Google sheet that we just used this 9:21 shows you I mean it goes way into the 9:24 weeds you can find whatever you need to 9:26 in terms of how to look up different 9:29 things depending on how it's listed on 9:32 the page that you're trying to look it 9:33 up 9:34 I found everything I needed here it took 9:37 some problem solving some sleuth work in 9:39 some cases but super powerful I hope 9:42 it's helpful for you and importing data 9:44 to your own spreadsheets let me know in 9:46 the comments below click like And 9:48 subscribe to the video really helps out 9:50 the channel you're awesome thanks and 9:53 goodbye 9:55 (MANDATORY REQUIREMENTS (" READ AND TRY TO RESPECT THE SCRIPT AS MUCH AS YOU CAN WHEN YOU EXTRACT THE CONTENT AND BRING IT TO THE HEYGEN PLATFORM. SO TRY TO CHANGE THE TEXT ONLY 1 PERCENT. TRY TO KEEP THE INTRO, AND OUTRO AND ORIGINAL SCRIPT AS IS. THANK YOU.")) ( IF YOU NEED TO MODIFY THE TEXT THEN INCORPORATE THE SOUND FILLERS BEFORE, INSIDE AND AT THE END OF EACH PHRASE FROM THIS LIBRARY: (" So, uh, what do you think about…? Hey, you know, I was wondering… Hmm, have you ever thought about…? Well, funny you mention that, but… Mmm, out of curiosity, how do you feel about…? Okay, just thinking, what’s your take on…? Ah, I’ve been meaning to ask you… Umm, on that note, have you noticed…? Like, I wanted to pick your brain on something… Right, this might sound random, but… Sooo, what’s your experience with…? You see, I was just thinking about… Actually, got a minute to chat about…? Honestly, what’s your favorite way to…? Basically, how do you usually handle…? 2. Use Conversation Finishers with Fillers Mm-hmm, so, what do you think? Uh-huh, would you agree with that? Right, how about you? Yeah, have you experienced something similar? So, tell me more about that. Hmm, can you elaborate a bit? Mmm, I’d love to hear your thoughts. Okay, what else comes to mind? Got it, how did that make you feel? You see, what’s your take on it? Makes sense, does that sound right to you? Conclusion? What would you say? Thoughts? Anything else to add? Feel free to share, okay? Alright, what’s next? Cool, what surprised you most? So, care to share a bit more? Yeah, how did that turn out? Mm, what’s your advice on this? Uh, what’s the takeaway for you? "hmm," "mmaaybe," "OK," "aaaa," and similar expressions to make the conversation sound more spontaneous and realistic. " Yessss!, Naaaahhh, mmmmaybe not, Whatevs, C'mon now, Heeeeyyy, Ooooh, really?, Ugh, seriously?, Awwww man!, Sooooo cute!, Hmmm... interesting!, Mmmmmaybe, Sooo what?, Totally!, Geez Louise!, Ahhh, okay!, Yaaas, queen!, No biggie!, You go, girl!, Whoa, slow down!, Easy peasy!, No worries!, Fuhgeddaboutit!, Gimme a break!, Hang in there!, Hurry up!, I'm so down!, It's all good!, Keep it real!, Not a chance!, Oh snap!, On it!, Peace out!, Right on!, So cool!, Take it easy!, That's a wrap!, That's deep!, Totally awesome!, Ugh, gross!, What's good?, What's up?, Who cares?, You bet!, You got this!, You're on!, Ain't no way!, All set!, As if!, Bummer, dude!, Bye for now!, Can't even!, Chill out!, Come on!, Cut to the chase!, Don't sweat it!, Easy does it!, For real!, Get it together!, Give me a break!, Go for it!, Hang loose!, How's it going?, I'm all in!, I'm down!, I'm good!, I'm on it!, It's a wrap!, Just saying!, Keep calm!, Keep it moving!, Let's do this!, Lol, okay!, Make it happen!, Mind blown!, No big deal!, No problem!, Not bad!, Not cool!, Oh well!, On the same page!, One more time!, Peace out, dude!, Piece of cake!, Right on track!, Rock on!, So true!, Take a chill pill!, That's awesome!, That's cool!, The best!, Too funny!, You're the best!, mmm, hmm, oops, uh-oh, ahhh, ohhh, ehhh, ugh, phew, shhh, whoa, yay, nah, meh, huh, duh, eww, ow, wow, ah-ha, uh-huh, uh-uh, tsk-tsk, hmmph, argh, grr, bleh, la-la-la, ooh-la-la, ha-ha, hee-hee, ho-ho-ho, tee-hee, yikes, whoopsie, whew, yawnnnn, sniff-sniff, gulp!, bam, bingo, okey-dokey, absolutely, for sure, you betcha, no way, no how, no dice, not on your life, no thanks, no way Jose, okay fine, okay cool, okay sure, okay whatever, okay then, okay now, okay got it, okay done, okay great, okay awesome, okay perfect, okay sounds good, okay no problem, okay you're welcome, okay thanks, okay sure thing, okay no worries, okay all set, okay good to go, okay let's go, okay let's do this, okay I'm ready, okay I'm good, okay I'm down, okay I'm in, okay I'm all in, okay I'm so down, okay I'm on it, okay I'm good to go, okay I'm ready to go, okay let's roll, okay let's rock, okay let's roll with it, okay let's make it happen, okay let's do it, okay let's get it done, okay let's make it real, okay let's keep it real, okay let's keep it moving, okay let's keep it going, okay let's keep it fresh, okay let's keep it cool, okay let's keep it fun, okay let's keep it interesting, okay let's keep it exciting, okay let's keep it awesome, okay let's keep it amazing, okay let's keep it fantastic, okay let's keep it incredible, okay let's keep it unbelievable, okay let's keep it mind-blowing, awesome sauce, big deal, big whoop, boo hoo, boo ya, bring it on, bye for now, cheers to that, chill pill, cool beans, cool story bro, crazy talk, cut the drama, don't be a hater, don't be rude, don't care, don't get it twisted, don't mess with me, don't sweat it, easy peasy lemon squeezy, enough already, for crying out loud, for real though, for sure thing, get over it, get real, give me a minute, give me a sec, go big or go home, go for broke, go with the flow, got it, gotcha, great job, great minds think alike, hangry, happy dance, hard pass, heck yeah, hello there, hey now, high five, holy cow, holy moly, how's life, hush, I feel you, I got this, I gotcha, I hear ya, I'm good with that, I'm on board, I'm so there, I'm with you, if you say so, in a nutshell, in your face, it's all good, it's on me, it's your call, just chill, just saying, keep calm and carry on, keep it classy, keep it lit, keep it real, keep it simple, keep on keeping on, keep on trucking, keep smiling, keep your chin up, knock it off, know what I mean, let's get this party started, let's keep it real, let's roll with it, life goes on, like for real, lol what, long story short, look who's talking, major key, meh whatever, mind your own business, my bad, my pleasure, my way or the highway, no bigs, no cap, no comment, no drama, no joke, no kidding, no lie, no offense, no problemo, no worries, not a clue, not a fan, not bad, not cool, not even, not impressed, not my problem, not on my watch, not so fast, not today, not yet, no thanks, no way, no way Jose, okay cool, okay fine, okay got it, okay sure, okay then, okay whatever, okay you're welcome, on fleek, on point, on the same page, one more time, one of a kind, out of here, out of sight, out of this world, peace out, peace out dude, piece of cake, pretty cool, pretty sweet, pure awesomeness, rad, really though, right on, right on track, rock on, roll with it, same here, same to you, say what, see you later, see you soon, seriously though, shh, shut it down, shut up, so cool, so cute, so done, so extra, so fake, so funny, so good, so great, so happy, so long, so not cool, so not impressed, so not true, so over it, so sad, so sweet, so true, so what, so yeah, sounds good, sounds great, sounds like a plan, spot on, stay cool, stay fresh, stay lit, stay real, stay strong, stay tuned, step it up, stop it, stop right there, straight fire, straight up, sure thing, take a chill pill, take a deep breath, take a hike, take it easy, take it slow, take it to the next level, talk to the hand, thanks a lot, thanks for nothing, that's a wrap, that's awesome, that's cool, that's deep, that's it, that's life, that's so cool, that's so true, that's the spirit, the best, the bomb, the real deal, the truth, there you go, think again, think outside the box, this is it, this is life, time to go, time to move on, to each their own, too bad, too cool, too funny, too good, too late, too much, too real, totally awesome, totally not, totally on board, totally stoked, totally true, totally yeah, touché, true that, try again, try harder, try not to, try to keep up, turn it up, turnt, uh-huh, uh-uh, umm, uncool, underwhelming, up to you, very cool, very nice, very well, wait a minute, wait for it, wait what, walk it off, walk the walk, watch out, way to go, we got this, we're good, welcome back, what's good, what's next, what's poppin', what's the deal, what's the plan, what's up, what's up doc, whatever floats your boat, whatever man, when in doubt, when in Rome, where's the beef, who cares, who knows, who needs that, whoa, whoops, whoopsie, why not, why so serious, win-win, with all due respect, word, work it, wow, yeah baby, yeah right, yeah sure, yeah yeah, yikes, you bet, you do you, you feel me, you go, you got this, you know what I mean, you're a star, you're awesome, you're the best, you're welcome, yuck, yum, zing. Mmm, Hmm, Naah, Naaaahhh, Mmmmaybe, Mmmmmaybe, Oooh, Oooooh, Aaaaah, Ahhh, Ah-ha, Ewww, Ewwww, Ugh, Ugggh, Yay, Yaaay, Yaaas, Yaaass, Whoa, Whoooaaa, Wow, Woooow, Meh, Meeeh, Huh, Huhh, Duh, Duuh, Ow, Owwww, Yikes, Yikeees, Yikesie, Whoops, Whoopsee, Whoopsie, Oops, Oopsee, Oopsey, Uh-oh, Uh-uh, Uh-huh, Tsk-tsk, Hmph, Hmphh, Argh, Arrrgh, Grr, Grrr, Bleh, Bleeeh, La-la-la, Ooh-la-la, Ha-ha, Hee-hee, Ho-ho-ho, Tee-hee, Heehee, Yawnnnn, Sniff-sniff, Gulp!, Bam, Bingo, Okey-dokey, Uh-huh-huh, Mmm-hmm, Hmmm..., Oooo, Aaaa, Eeee, Oooooh yeah, Mmm-mmm, Huh-uh, Hmph-hmph, Tsk-tsk-tsk, Grr-grr, Bleh-bleh, Yay-yay, Whoa-whoa, Wow-wow, Meh-meh, Huh-huh, Duh-duh, Ow-ow, Yikes-yikes, Whoops-whoops, Oops-oops, Uh-oh-oh, Uh-uh-uh, Tsk-tsk-tsk-tsk, Hmph-hmph-hmph, Argh-argh, Grr-grr-grr, Bleh-bleh-bleh, La-la-la-la, Ooh-la-la-la, Ha-ha-ha, Hee-hee-hee, Ho-ho-ho-ho " )) Goal here is to transform content from script videos in to a video scripts and long articles. So we want to fully automatize the script extraction from youtube and then after we have whole script we want to run 6 different prompts, one to create a personalized script and the other one to create long wordpress articles and one for social media post to support the video posts in key platforms ( linkedin, instagram, mastom, tiktok and facebook ) CREATE A SCRIPT ABOUT THE How Educators Use AI Video to Teach Smarter USE A LANGUAGE AND TONS FOR 16 YEARS OLD FROM URBAN COMMUNITIES IN GEORGIA. CREATE A 500 WORDS SCRIPT. ADD INTRO, OUTRO AND CALL FOR ACTION. CREATE A SCRIPT ABOUT THE How Educators Use AI Video to Teach Smarter USE A LANGUAGE AND TONS FOR 16 YEARS OLD FROM URBAN COMMUNITIES IN GEORGIA. CREATE A 500 WORDS SCRIPT. USE ONLY WORDS AND PHRASES USED BY TEENS ALL THE TIME. USE ONLY TEENS LINGO EVEN IS HARD TO UNDERSTAND. 90 PERCENT IS TEENS LINGO AND 10 PERCENT CASUAL LANGUAGE. CREATE A SCRIPT WITH NO LESS THAN 1000 WORDS AND USE ONLY LINGO FOR URBAN TEENS