Automatic Screenshots in Apps Script

Advanced

About this Tutorial

This tutorial has no description yet.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Apps Script APIs used

DriveApp SpreadsheetApp UrlFetchApp

Apps Script methods used

.createFile() .createFolder() .getBlob() .getFoldersByName() .getUrl() .appendRow() .getActiveSpreadsheet() .getSheetByName() .fetch() .getContentText()

Video Transcript

<div>00:00 So let's create automatic screenshots in our Google Sheets. So we can select URLs and get that screenshot. We're going to use screenshot1.com. 00:10 It has a hundred free API calls, I believe. Um, and there's a lot of reasons we will do this. One, maybe we want to create our own archive of a particular page. 00:22 Like, my front page is Jira. Extract URLs from Google Sheets Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

It's bettersheets.co, and yes, I can go to, like, Internet Archive and see the past results. 00:28 But for some marketing purposes or quick screenshots, maybe I want to screenshot it every week because I'm doing some kind of experiment. 00:36 Or, I want to look at competitor pricing. Um, I'm going to look at their pricing page. I want a screenshot of that. 00:42 I want to save that screenshot. Roasting Google Sheets Automatic Screenshot in Google Sheets

Maybe I want to analyze it later or grab a snippet each week and just have it available. 00:50 Or, I want to look at, like, Google search results and see exactly what's going on, not just scrape the results, but literally see, okay, go to the page, see the results, see if there's AI stuff going on. 01:03 I want to, like, look at it. Or, I want to, like, screenshot a Google Sheet, for example. When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa Automatic Screenshot in Google Sheets

Anywhere where you can go to the URL, you can grab a image of that page, and you want to save it, this is going to be a very helpful tutorial for you. 01:18 Uh, we're going to actually do two things in this video. Automatic Screenshot in Google Sheets

We are going to grab the screenshot, which I've made a video of this before, exclusive to members. 01:29 But now, I'm going to try to automate this, because I want to say, hey, go to this Webflow.com http://www slash price thing, every week, once a week, at the exact same time, and grab a screenshot, and save all of those into a drive. 01:45 Alright, we are going to start from blank, we are going to try to just get the screenshot using the screenshot API, Screenshot One API. 01:53 You need to go to Screenshot One, you need to go and get your access key, that is all you need from Screenshot one. 02:00 Every day. Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

Everything else will be in this video. So we are going to call this AutoScreenshot. I'm going to create a function called GetScreenshot. 02:17 I'm going to go to some target URL, because we are going to use a few different URLs. I want to say what is the websites URL that we're actually going to get the screenshot of. 02:27 We're going to need the API URL. We're going to need the URL of the actual cached image . Uh, we're going to end up at the end with some kind of URL for even the Google Drive saved image . 02:40 So this target URL is called target URL, not just URL. Extract URLs from Google Sheets Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

Above this, as a blanket, sort of. Overall variable, I'm going to write API key and you put your API key here. 02:55 I'm putting mine. And of course I'm going to regenerate mine after I make this video, but you just put yours there in quotes and all caps API key. 03:03 We can now use that anywhere in our function as a variable. Created a GPT function in Sheets GPT in SHEETS Automatic Screenshot in Google Sheets

I just want to make that clear that this is where you can edit this. 03:11 If you are a BetterSheets fan, member, and you're watching this on BetterSheets, this sheet, exactly this sheet, is down below. 03:19 You can just copy it and use it and edit it as you wish. So we're going to need the API URL. 03:26 And this is going to be That's the entire API URL. And then we're going to need to encode this. GPT in SHEETS Automatic Screenshot in Google Sheets

The reason is we want to make it so that we can actually access it through a URL fetch app. 03:49 Like, it's an, like, if we write some spaces here or things, they're going to, we want to change them into, literally, a URL can use that letter. 04:00 So we're going to say, encode encoded URL equals encode URI component and put our target URL here. 04:17 So the URL that we're grabbing from our sheet, just in case it's Oddly, maybe we're copying and pasting it from somewhere, we just want to make sure that it is correctly accessible to put into a URI or URL. 04:37 And now we're going to take this API URL, our target URL, and we're going to combine them into a single URL that, that, that, then we can use URLFetchApp to go and get. 04:53 So basically we're going to call this API. Extract URLs from Google Sheets Automatic Screenshot in Google Sheets

If you haven't worked with APIs before, I would recommend going and doing Spreadsheet Automation 101. 05:03 That course will take you through very basic steps of coding and get you to APIs and explain all about APIs very quick, not quickly, but very quickly. 05:13 Basically, we just need to go to a URL and get the response there. But the URL that we want is going to be pretty crazy. 05:22 We're going to call this full URL. And I'm going to put it in backticks because we're going to fill this with variables. 05:31 Basically, we have first this API URL. Spreadsheet Automation 101 Lesson 4: Access APIs Introduction Getting Started Coding in Apps Script Access Free API's with Google Sheets Automatic Screenshot in Google Sheets

So we're going to do dollar sign . And curly brackets. And put that API URL. 05:43 That's going to be the start of our URL. Then we're going to put a question mark, access key. And this is all documented on screenshot1's API, how to access their API. 05:57 I'm just doing it for you here. Now we're going to equals, we're going to put in our API key. But we can't just do this, just copy and paste API key. 06:06 We have to do the same thing we did just before, which is put dollar sign curly brackets around it. GPT in SHEETS Automatic Screenshot in Google Sheets

This is called interpolation, and we're saying, hey, yes, we're going to give you this text, this text, but we're going to grab this text from somewhere else. 06:22 Okay, now we need to say URL. Equals, and do you get the last, do you already, a step ahead of me, we're going to put this encoded URL in there. 06:37 So we're taking the API key, we're taking the API URL, and the encoded URL, we're all putting it together, but we have to have a few more little pieces of information. 06:47 So we're going to put an ampersand. Automatic Screenshot in Google Sheets

I'm going to say format equals jpeg, we're going to say ampersand cache equals true, we're going to say ampersand cache underscore TTL, this is just saying how long do you want the cache to be available for, this is like seconds, two five nine two zero zero zero, that's 07:12 a lot of seconds, Thanks. That's 43,000 minutes, 720 hours, it's about, let's see, 60 divided by 24, that's about 30 days of seconds. 07:30 And we need one more thing, response type equals JSON. Automatic Screenshot in Google Sheets

So we're saying, hey, when we tell you this URL that we're going to go to, we have the correct access key, what kind of response do we want back? 07:47 We want JSON back. JSON is a very easy-to-read-for-computers kind of language. So this computer and this function is going to be able to read this JSON. 08:01 And we're going to tell it how to read it. Access Free API's with Google Sheets Automatic Screenshot in Google Sheets

We're going to say, turn JSON capitalized, and this is saying, this is a, JSON capitalized is a function inside of Apps Script that allows us to parse JSON. 08:14 What we're going to say is, URLFetchApp .fetch, and go grab that full URL, and then just on the other, between these two parentheses, getContentText, and then outside we're going to say .cacheURL. 08:35 Now what all this is doing is saying, hey, go fetch that URL that we've just created, get back whatever, get back whatever content text you have that tells what the response is, then parse it, but then only, of all of the information you're giving us, only tell us what is the cache URL. 08:54 There's a whole lot of other information when you get this response, but honestly all we need right, right now, all we need is this URL. 09:02 We could deal with response codes and say, okay, if it's 200, give us the cache URL, if it's something else, give us the error , but for this particular video, we just care about the URL. 09:13 And this function , getScreenshot, is going to go to that target URL at the API and give us back some image . 09:22 Actually, it's going to give us back, sorry, it is going to give us back not the image , but the URL to the image . 09:28 So I'm going to hit save, and I'm going to go try it. Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

I'm going to say getScreenshot and see what happens. 09:35 So, we have to now nothing equals getScreenshot. I'm going to put this URL in here, bettersheets.co, and I'm going to hit enter and see, do we get an error ? 09:47 Probably. So we say, okay, this is all saying basically it didn't work. But, let's see if we can do something else. 09:59 Let's Let's see, maybe it is a permissions. Let's hit run. I'm going to hit continue. I do believe, maybe it is, that it is just this HTTPS there. 10:37 That we just need a full URL. That is exactly the issue. Automatic Screenshot in Google Sheets

It was just that we needed the exact full URL. 10:42 HTTPS, slash, slash, and it works. So we can actually go to this URL and see what is at this URL is the image . 10:50 All we're doing here in B is saying, wrapping that URL around image . Let's get screenshot for A3. We're going to get an error . 11:02 Let's do HTTPS. Boom. It works. And let's do the next couple. Just to make sure it's working for all of these URLs. 11:14 These kinds of things. Actually, I'm, I'm really interested in this Google search. Extract URLs from Google Sheets Automatic Screenshot in Google Sheets

Oh, funny enough, this Google search gave it an error . 11:23 It's, it's doing this thing that Google has done now recently. It's like, you need to authorize it. You're a human. 11:29 So, that's not working. But the screenshot of a Google sheet is working. Pretty cool, right? That basically we can do this. 11:36 Yes, Google search is not working. But only because it's not really a human doing the screenshot. All right, let's save this. 11:45 So, now that we know that this screenshot works, we need the full URL. Let's save it to a drive. Extract URLs from Google Sheets Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet Automatic Screenshot in Google Sheets

So, we'll create another function , Then save screenshot to drive. 12:02 And what this is going to do is, we don't necessarily want this, like, in a sheet like this, like, we're just going to list the URL and then go grab the image . 12:11 We know the URL we want, let's say this, like, webflow pricing or bettersheets.co slash pricing, right? Uhm, we know that URL and we want to go to it every week. 12:24 So we're going to say, const cacheURL equals getScreenshot, we're going to put in quotes anything. 12:52 You can have that trailing, whatever. Extract URLs from Google Sheets Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

So we're going to grab the screenshot here. Now what's interesting is we save this cache for 30 days. 13:05 So you might want to decrease that amount. If you do, then you'll get a new screenshot. However, in my particular case, screenshot one only gives you, like, a hundred for free. 13:20 Then it starts charging you if you want to be charged, you know, depends on how many screenshots you really need. 13:26 You need them every week, every day, every month, every two months, whatever that is. We're going to create a blob. 13:37 Why do I say blob? I say blob. Automatic Screenshot in Google Sheets

Because that's how files are saved in Google Drive. So we're going to say URLFetchApp .fetch. 13:48 Go grab that cache URL. And again, we're not getting We're getting the response URL of screenshot one. 14:02 And then we're saying get blob. And we want to set the name. This is very particular because we want a consistent screenshot name hyphen and then I'm going to add dollar sign and do some interpolation here of new date. 14:26 And I'm going to just say dot to ISO string . Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

This is just saying, hey, create a new timestamp , but call it a ISO string . 14:41 And then at the end, dot JPEG. Okay, so we're just saying put a date and time here of whatever this file is. 14:50 Now, the folder. This folder situation is very complicated. Drive sort of makes it weird. We do const folders equals drive app dot get folders by name. 15:08 And we're going to search for Better Sheets Screenshots. You can name this anything you want. You just need to know what is the folder name. 15:18 Yes, you can create a folder and then put the name here. However, you don't have to. Automatic Weekly Backup of Google Sheets Automatic Screenshot in Google Sheets

I'll show you why. 15:25 We're going to let folder. That's just a variable that says, here's a random variable, just a blank variable, not random, blank, called folder, and if folders.hasNext, get it. 15:39 Yes, folders.

Go up to this folders, do .hasNext, and if that's true, meaning there is a folder there, then we're going to do folder equals folders.next. 15:54 So, we're saying, hey, if you go and get all the folders by name and there is a next one, that next one, that's the name of the folder, and that's the folder we want one. 16:03 However, else, actually put this on a new line, there is an else, we have to deal with else, meaning if this folder doesn't exist and it's false here, doesn't have a next one. 16:20 Let's go make it. We'll say folder equals drive app. Automatic Certificate Creation in Google Sheets

Create folder and we're going to name it the exact same. 16:35 So basically all of this is saying, hey, go look for the folder called BetterSheets screenshots. If it does exist, give us back that folder. 16:48 If it doesn't exist, go create it. And now we're going to return, so this is the end of our function here, we're going to return folder.createFile, blob, this blob up here, this is the JPEG we're saving, and we're going to get the URL. 17:15 Now, where do we want to get this? Automatic Weekly Backup of Google Sheets Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

We can call this a variable, we can just say, variable, screenshotURL equals this, and instead of a return, let's append to somewhere, let's create a sheet, called saved, Thank you. 17:39 And we're going to just append here, in the A column, this saved URL. 17:51 We're going to get this URL. So, we're going to say, spreadsheetApp .getActiveSpreadsheet, getSheetByName, saved, dot, appendRow , and what are we going to append? 18:04 The screenshot URL. In, and this is, let's bring this up to the top, this is in square brackets. Getting Started Coding in Apps Script Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

Okay, so every time this runs, it's going to append, right here, a new URL. 18:22 Maybe let's say date, as well. Let's put a, the new date here. So how do we do that? Let's go back to our, here, and we're going to do a comma, new date, and it'll have a timestamp . 18:42 So let's run save screenshot to drive. We're going to select it up here, save everything, or click save project to drive. 18:49 Let's hit run, we may need more authorization . Waiting patiently to see if we have any errors. 19:12 Let's go. We have a URL. We have a timestamp . Great. Writer Better Prompts Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

And, if we go to that URL, it is saved. 19:24 Let's go to our drive as well. And there's BetterSheet screenshots, and there it is. So if we run this again, right now, obviously this site hasn't changed, but if we run it again, it's doing all of its work. 19:43 There's another Google Drive file. Fantastic, right? So how do we automate this? Let's go back to our Apps Script . We have two functions , GetTheScreenshot and SaveTheScreenshot. 19:57 SaveTheScreenshot actually uses GetScreenshot, which is cool, which sometimes, you like to put it like this. Extract URLs from Google Sheets Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

Sometimes, you can do it like that. 20:10 SaveScreenshots at the top. But how do we automate? Let's go over to the left side, go to Triggers. On the bottom right, there's a big blue button called Add Trigger . 20:21 Choose which function to run. This is a drop-down menu that shows all of our functions . If you have other functions in your Thank you. 20:27 sheet, you're going to have to find it, and we're going to use SaveScreenshot to drive. Select EventSource. It's going to be time-driven. 20:35 Select type of timer. I want to do it every month. What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

On the first of the month. 20:46 Great. And I'll do it at 5 to 6 a.m. These are all things you can change yourself, and now we have a Apps Script . 21:00 Trigger , an automation that has been created. If we want to delete this and we don't want to save these particular screenshots anymore, click the three buttons to the right and delete trigger . 21:15 Delete forever. If you want more than one, we need to do a couple things. This is only one site. Can ChatGPT Automate Spreadsheets? Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

Save, you can call this Save Better Sheets. 21:28 Then we can copy this Save Better sheets, paste it, underneath it, and say Save Webflow Pricing. And we're done. Let's go get that URL over here somewhere. 21:46 And we can change the URL right there. And now we have a whole new function that's saving this. Now let's save. 21:58 And if we go over to triggers, we can create two triggers. One that saves time. Gives better sheets, and one that saves web flow. 22:06 Add trigger . And now we have two functions here. Extract URLs from Google Sheets Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

They are both using this Git screen shot, which is totally fine. 22:14 And actually, really nice functionality here. But if you don't want to be copying all of this stuff, there is one other way. 22:28 We can do this. Save URL to drive and put in URL here. And instead of putting this as a, ah, text, we can just put it as a variable URL. 22:43 And we can save this URL and make that the variable there. Every time we save this URL, we may want to rename this folder, okay? 22:59 So these are going to be all the screenshots of everything. Maybe you want a unique folder for each URL. /Copy URL Trick Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

So let's try to do that. 23:07 We can put, instead of these quotes, we can put backticks. instead of better sheet screenshots. Let's call it screenshots4, and do interpolation as well here. 23:31 And literally the URL that it is, is going to be in the name of the folder. Let's see if that works. 23:39 So, we need, We need a trigger to trigger this. So, we actually need to create another function , savebettersheets, put that in quotes, and run save URL to drive, and do HTTPS, bettersheets.com. 24:04 Now, much simpler function to run. GPT in SHEETS Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

We can call this savewebflowpricing, again grab this URL. 24:23 So, what's going on here is we are creating a little function that just says, hey, go to this URL, I'll save to drive and use this URL. 24:32 However, I want to, I want to share one cool thing we can do here. We can add multiple variables, save this URL. 24:38 So, we can say, here's the URL, here's the folder name, and here we say, BetterSheets.co. And here, with, for Webflow, we say, Webflow Pricing. 25:01 And instead of screenshots for save this URL, we put that folder name there. Extract URLs from Google Sheets Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

So, this is a really cool way to say, hey, yes, I want to save the Webflow, I want to save this URL, and I want to call the folder this. 25:14 So, now, every time we run this, it's going to save that URL, but use a different name for the folder name. 25:21 Pretty cool, right? And then, all we have to do is automate this function for Save Better Sheets. Extract URLs from Google Sheets Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

All we have to do is automate this Save Webflow Pricing. 25:31 We can even test this out, just to see if this is work, working, and see if we have a Webflow Pricing, uh, folder. 25:40 So, it looks like it all worked fine, and here it is, screenshots for Webflow Pricing, And every time we run this, Save Webflow Pricing, if we've automated it, it's going to just add to this drive folder. 26:17 Pretty cool, right? Automatic Screenshots in Apps Script Automatic Screenshot in Google Sheets

This is awesome that we can do automatic screenshotting from Google Sheets with Screenshot One, and do it in a way where we're saving this to a particular file. 26:28 Google Drive folder. Again, if you're watching this not on BetterSheets, on BetterSheets.co, all members have access to this exact file. 26:39 Go grab it. Become a member of BetterSheets today. Thanks for those who are already members of BetterSheets.</div> Automatic Weekly Backup of Google Sheets Automatic Screenshots in Apps Script Track Anything! In a Google Sheet Automatic Screenshot in Google Sheets