Curate Google Sheets Easily - Automatic Bookmarklet

<div>00:00 Okay, we're trying to get a list of websites and their titles into a Google Sheet and what you're doing now, you might be doing, going to like stripe.com, I wanna go there, I wanna get the title, so you go to URL, you paste it here and then you put in the title there, right? 00:16 You don't have to do this anymore. Let's create a bookmarklet.

I've created a bookmarklet before for members this one is different cause it's gonna automatically create the bookmarklet for you. 00:26 Used to be I'd describe to you like a javascript function and stuff, no need, I got you, I wrote the app script and the automation to do this without knowing how to code. 00:37 So what we're gonna do is we can go to Stripe and in our bookmark bar, I have this plus company, I have this, I'm gonna click on it and it will grab the title which is not just Stripe. 00:48 Here and it's the URL stripe.com, I will hit submit and already it is in my Google sheet right there.

Stripe financial infrastructure. 00:57 If I go to any website now, I can click that button plus company. For instance, I'm here on Netflix. I click plus company there, Netflix, okay it's in Indonesia, whatever. 01:09 It is the one that's available to me and it is now inside of my Google sheet just by that. Just a couple clicks instead of having to copy paste all that information. 01:18 Uhm, so what are we doing here?

We are using Google Chrome bookmark, we're using a little JavaScript function , and we're using Google Forms , and we're gonna use Google Sheets and Apps Script to generate this. 01:30 Uhm, you're watching this, you are a member, you can get this sheet for free down below.

Uhm, and I will show you the Apps Script here, extensions, Apps Script , we'll walk through a little bit of it, but let me show you what it's gonna create. 01:45 Uhm, it's going to create automatically a form, so you're gonna get this sheet, it's gonna be file, make a copy, that's what you're gonna gonna do, gonna make a copy, it's gonna look boring, it looks absolutely boring, there's nothing in the sheet, except for settings page, which is super important,  02:01 and you'll see why soon.

Okay, so I, I copied it with the uhm form, but we're gonna unlink the form, you don't have to do this, it won't have this, we're gonna just delete all this. 02:16 You'll copy it, it'll look like this, settings, probably B blank here as well, and this B2 place, okay? If once it's in your, your once it's in your drive, all you have to to do is go up to this custom functions , we'll show up menu, create form and bookmark it. 02:29 You're gonna have to run it once you're going to have to authorize it, and then run it again.

That's it. 02:36 So here, this is what the authorization 's gonna look like, but you're gonna authorize it, then run it again. You're gonna want to have to, it's gonna want to edit your Google Docs, Sheets, and Drive, that's, Google Docs is your form, Google Sheets is the Google Sheet you're looking at now, and the Google 02:48 Drive is to make sure that you have a form I'll click allow, and now, I'm gonna go click it again. 02:55 Okay?

So, create form and bookmarklet, once I authorize it, it is going to create a form, and then it's gonna create the JavaScript for us here, that we're gonna put into our bookmarklet, so it's right here, if you want it here, totally okay, but it will also write it in B2 of the settings. 03:12 Page, so you don't, don't make, make any changes to the settings page.

Okay, here's all of the gooblygop you'd need, this is the Google form , the CURL is your Google form , none of this script is going to my servers at all, don't worry, it is in your Google Drive, using your Google forms . 03:28 But it's called companies I'll show you how to edit that later, but now it is a fresh Google form and you have this JavaScript, so what we're going to do is go up to the Chrome URL thing, we're going to just click and drag our URL into our Google our Google Chrome bookmark area, if you don't have it, 03:48 shift, command B hides and unhides it, but now that I have something that I can edit, I actually don't want this Google sheet, I want to right click on here, I want to edit and the name I'm going to say plus something, whatever I'm trying to add to the sheet, it's just a little reminder for me, this  04:05 is just my own way to do this, plus companies or plus website or plus site, maybe all caps site, plus and in the URL, this is important, delete all of my stuff in the URL, and command v paste the JavaScript function at the very beginning, it's going to look like JavaScript, colon, and it has all this 04:26 stuff, and what it's doing is it's just creating a new tab that goes to your Google form , it pre-fills the Google form with the title, pre-fills the Google form with the URL that you're at, and so it'll capture all that data into that form already for you, and all you have to do is click submit. 04:42 So now we've saved it, and let's say we want to go to another site, let's say we want to go to, let's just do apple.com, there we go, we're gonna go to apple, actually app sumo and app sumo.com, we want the title, we want stuff we're gonna click plus site up here on the apple one where also gonna click 05:05 plus site, go back to our form, here it has the title, it has the URL, click submit, go here to apple, URL, click submit. 05:14 So we have now two things in our sheet, let's go check, is it in there?

There it is, there they are. 05:20 Super fast, super easy. Alright, how is all of this working? Now you have it. If you do not care at all about the app script and you just want to use this, go ahead and grab this sheet down below. 05:33 Get it, copy it, use it, what for whatever you, Want, if you do use it, please email me, Andrea at BetterSheets.co. 05:40 You remember, let me know how you're using it. I would love to show this off to others on how you're using it and get some more use cases. 05:48 Cause I do this all the time with curated lists of information.

I used to- Thank you. Use this to curate a newsletter of articles. 05:56 I did like 20 articles a week at curated. It was already in a Google Sheet just from reading like a hundred articles a week. 06:03 I would then add 20 to the Google Sheet each week and then I would have them timestamps. So I knew, okay this is what I added this week. 06:10 Super easy. If you're a member now umm. If you're a monthly member you won't be able to get this but if you're a lifetime member you can get better letters. 06:20 It's a tool I offer completely for free. If you are a monthly member.

Thanks for uhm you'll have to pay for it for better letters. 06:27 This is included in that. It includes a number of other things but this is how I powered the newsletter creator and better letters sheet. 06:35 It was really really cool. Here rated a ton of articles. Curated ton of stuff every week. I'm putting a newsletter that makes curation so so so easy. 06:44 Okay, now how did I do this? Let me walk through the app script to share with you first. The on open function on open allows you to create a little button. 06:53 That's that button up here custom functions .

We'll a few Bye. Function one we're going to create a form. What we're doing is we're going to go to the set settings. we're going to grab the name. 07:12 So anytime if you want to change the name of the form just go up to B1 here in settings and name it whatever you want to name it. It's going to go grab that name. It's going to go to form app. 07:16 We're using form app the in app script and we're going to create a new form. We're going to name it whatever name is in that B1 cell. 07:26 We're then going to set the destination. This is so interesting.

We're going to set the destination of the form to the current sheet . 07:33 And this is actually pretty hard in app script . It is insanely easy in Google Sheets because you have a function up here of where is it tools create a new form. 07:42 Once you do this action your self it will it will add the results and the responses to the sheet. But when you do it through app script you have to set the destination programmatically. 07:57 And the destination is going to be a sheet ID of the current sheet here.

We're just grabbing the get active spreadsheet dot get ID we're just getting the active spreadsheet we have now. 08:06 Umm. We also are getting the ID of the form and the published URL because we're going to create the you are the JavaScript function . 08:16 First, the first thing we need is two things the title and the URL. I also added notes here if you want to add another section. 08:23 Umm and then we're going to rename the it's going to create the form responses.

We're going to rename it here to whatever the name of the form. 08:33 Now, we're going to run this next function which is get form entries. And what this does is it takes the ID of the form, the URL of the form, and it creates this JavaScript function . 08:45 And all that is is JavaScript, window.location.

Put the URL, which in this case is the URL of your Google form , adds this sort of entry text, gets the entry, so the entry ID of the form is like which one is named title, which one is named URL, and then it fills it in here for you, and it says okay, for 09:12 the document title put it in this entry, for the URL, which is window.location.h, put it in this entry, they are matched now. 09:20 And so when you click that button it grabs the title and it puts it in the correct entry pre-filled form, it generates a pre-filled form. 09:27 This is form response with item response to pre-filled URL.

It hits a pre-filled URL. This is utterly magic.

This absolutely feels like magic on the other side of it. 09:39 We're gonna create this whole URL all we're gonna add all together, concatenate it all together, and then set that value into a umm what I do is I put in an alert, but just before the alert I actually put it in this b2 place so you can see the whole thing here everything's added together all the entry 09:57 IDs from that form are in the right place and we also have it saved here but we also get an alert that says here it is if you want to copy it right away. 10:06 That's the whole app script .

And I think this is one of the coolest things ever in Google Sheets because we're using Google Forms , we're using pre-filled URLs and we're doing it programmatically without having to go through the setup. 10:18 If you want to do this manually you have to create a form, you have to go Thank you We'll see you Create a pre-filled form, figure out the entry numbers, the entries of each one, then go create that JavaScript and put it together. 10:31 And we're doing it in a few moments programmatically. It's so much fun.

Let me know what you're going to use this for if you're creating curated data sets if you are putting together a list for your boss of like, oh umm articles or websites that do this or LinkedIn profiles or TikTok profiles, right? 10:51 Makes it, this makes it so easy to take websites and put them into You Google Sheets because now we have that data here and we can copy this into another page add contextual information we can create data sets to sell if you're looking to sell spreadsheets check my check out the course telling spreadsheets 11:07 if you are looking to make cooler sort of dashboards maybe with a lot of data check out better dashboards check that out also I just recently made a video taking someone else's list of websites and turning it into a really cool branded lead magnet freebie combine that video and this video and you're  11:29 gonna be creating tons and tons of cool new products you could probably create a new product every single week for the next year with this alright I'm excited to see how you're using this book market this automatic creation of a book market with google forms thanks so much for watching bye</div>

Curate Google Sheets Easily - Automatic Bookmarklet Maker