Script It! with Google AdSense

ARUN NAGARAJAN: –episode of
Google Developers Live. My name is Arun Nagarajan, and
we’re actually coming live from our London studios. I’m usually in the Google Apps
Developer Relations Team, and you probably have seen me from
the New York office and Mountain View office before, but
it’s my very first trip to London and today’s episode is
extra special because we have an engineer from our team. NICK JOHNSON: Hi. I’m Nick Johnson from
the AdSense API Team right here in London. The AdSense API gives you
programmatic access to your AdSense account. ARUN NAGARAJAN: Excellent. And it’s actually, I should say,
sunny in London, which I was surprised by. I think I packed a lot of
sweaters and jackets that I don’t need, which is good. NICK JOHNSON: I think we were
all surprised by that. ARUN NAGARAJAN: And you said you
were at a ski trip where it was warmer– NICK JOHNSON: Yeah, it was
warmer in the ski resort than it was in London. ARUN NAGARAJAN: That’s
awesome. I’m glad to be here. Today’s episode’s extra special
because what we do is a series called Script It!,
which basically is all about using Apps Script in
interesting, innovative ways where you can build something
that you may not normally think about with Apps Script. So Apps Script is all about
taking Google Apps and doing more with it, whether it’s
extending the UI to show a menu within Spreadsheets,
or taking the data from Spreadsheets and exposing that
to another service, into Drive, for instance, or
automating things so every half hour, check for an email
from a particular person and then alert me if something
interesting happens. So integrate, extend, and
automate are the three things about Apps Script that are quite
common in different apps that we’ve seen, workflow
apps and so on. But AdSense, which I’m not
super familiar with– I kind of caught up with Nick
earlier to figure out what the product is about what the API
is for, and if you can walk through what the product is for
and what the API is all about, that would
be really good. NICK JOHNSON: OK. Well, AdSense is a monetization
solution, lets you monetize your website
based on ads. ARUN NAGARAJAN: That’s great. NICK JOHNSON: Very simple,
very straightforward. ARUN NAGARAJAN: So if I’m a blog
publisher, I’d want to drop in AdSense into
my page if I’m– NICK JOHNSON: If you’re a small
blog publisher, as I myself am, you can just very
easily fire up an AdSense account, stick a small ad on
your blog, and receive a check in the post to a
certain extent. ARUN NAGARAJAN: That’s great. Can’t complain. And then what’s the
API all about? Why is there an API? And can you show a little
bit about the API? NICK JOHNSON: Well, yeah. Basically, there are essentially
two main use cases we think of for the API. The first is if you’re a larger
organization which is using AdSense and you have
a need to use your own dashboards, your own systems,
you don’t want to use the AdSense front end as it is. You can use the API to pull
in your reporting and inventory data. Secondly, you might be a
developer who is building a mobile app or a desktop
app, a web app. So if you look in the IOS or
Google Play Stores, you’ll see plenty of examples
of those as well. ARUN NAGARAJAN: Cool. So a lot of folks are already
using the API heavily. Excellent. And so can you show
where someone can learn more about AdSense? NICK JOHNSON: Yep. Well, if you look on the Google
Developers page here, you can find the AdSense
Services from within Apps Script. So as you see, we have a fairly
comprehensive set of documentation for that. ARUN NAGARAJAN: And to be clear,
AdSense has two APIs. The API that we expose from
Apps Script is the AdSense Management API, correct? NICK JOHNSON: Yes,
that’s correct. We also have a second product,
the AdSense Host API, but that’s not really
relevant here. It’s a different product
entirely. ARUN NAGARAJAN: Got it. And can you point out
the page again? It’s under
apps-script/serviceAdSense, that’s right? NICK JOHNSON: AdSense,
that’s correct. ARUN NAGARAJAN: Under So it’s pretty easy to find. So can you show a little
bit about the code and what can be done? NICK JOHNSON: OK. Well, what we have here is a
fairly simple example we put together using Apps Script. So if I just go over to an
example AdSense account, I have a spreadsheet here, an
ordinary spreadsheet. ARUN NAGARAJAN: Let’s take
a look at the screen. NICK JOHNSON: I’ll just roll
up my sleeves, and you’ll notice, those of you who are
familiar with Google Docs Spreadsheet, there’s an extra
menu on the right here which we’ve added using Apps Script. So I have New Report, Refresh
Report, which I’ll come to shortly, and then a couple of
preset reports from the AdSense front end. So if I hit Overview Last Month,
it’ll think about it for a moment, fill in some
columns, start date. So what I have here is I’ve
pulled up a very basic report of your account for the
previous month. So you have dates, number
of ad requests, coverage, which is– ARUN NAGARAJAN: And it knew
which account to dial into because who you were logged
in in the spreadsheet. NICK JOHNSON: Yes, it uses the
account you’re logged into as the spreadsheet. ARUN NAGARAJAN: Very cool. So you didn’t have to
reauthenticate or anything. NICK JOHNSON: No, none
of that at all. And if you didn’t want to use
one of the presets, I can hit New Reports, and I’ve built a
very basic kind of UI which lets you assemble it. But if I just move away from
that for a moment. This is one of the cool things
which you’re able to do with Apps Script. If I realize actually,
this wasn’t the data I wanted at all. I didn’t want last month. I wanted the last two months. So remembering how American
dates work, if I change that to a two and then I just come
up here and refresh this report, give it a moment to
think, and just like that, there’s no further data. So that wasn’t the
best example. ARUN NAGARAJAN: So
all the money was made in the last month. NICK JOHNSON: All the
money was made in the last month here. So let me try something
else instead. Let’s decide instead, let’s say
I’m not interested in ad requests at all, but I am
interested in how much money was made by each of the
different ad clients or products which we used here. So if I just change this to Ad
Client ID, hit Enter, and refresh the report, I now
have the same data but– ARUN NAGARAJAN: That’s
really cool. So what you’ve been able to do
there is the header actually corresponds to fields
in the API? Is that correct technically
speaking? NICK JOHNSON: Yes. ARUN NAGARAJAN: But as far as
the user is concerned, they’re just deciding which
column to see. NICK JOHNSON: Exactly, it’s
just a spreadsheet. ARUN NAGARAJAN: That’s great. NICK JOHNSON: And I can
actually, we added as well another neat little thing we
were able to do is I’ve got a Help thing here, which we
figured, why not just use a spreadsheet? So we have a list of all the
different dimensions and metrics which you can use. ARUN NAGARAJAN: It’s
a nice trick. So is that a hidden sheet,
or is that something that automatically populates? NICK JOHNSON: No, this is
generated on the fly by the Apps Script itself. ARUN NAGARAJAN: Very cool. Very cool. So how does this magic work? So what did you have to do to
pull this all together? Can you reveal your tricks? NICK JOHNSON: Well, that’s a
very good question and I’m glad you asked it. ARUN NAGARAJAN: Wasn’t
set up at all. NICK JOHNSON: Actually, why
don’t I just take a step back and say Tools, Script
Editor here. So if I move over to that window
now, I’ve blown the text up nice and big
so you can see it. First of all, this gives away
some of the magic right away. On the left hand side, you
will see the names of dimensions and metrics here. And on the right hand side,
I’ve put more friendly, English versions of them,
which is what’s used to generate the Help page. ARUN NAGARAJAN: Got it. That makes sense. So you have a map of what makes
sense to the user versus what the API uses. NICK JOHNSON: Precisely. ARUN NAGARAJAN: Got it. OK, cool. NICK JOHNSON: And then I
have them ordered as well, just to simplify. ARUN NAGARAJAN: What that’s
also good for is you could localize it, potentially. So you could potentially have
a Spanish version or French version, where they may type in
what makes sense to them in that locale. If that matches up to that same
tag, then you will get the right data. NICK JOHNSON: Yes. That would work to some extent,
although some of the slightly cheesier hacks I’ve
used here wouldn’t work in that case, but we’ll come
to those later. ARUN NAGARAJAN: But
that’s a potential exercise for the viewer. NICK JOHNSON: And then just
here, these are the lists of dimensions and metrics which
are used for the Overview Reports which you
saw on the menu. And then second, I used one of
the special functions that’s available in Apps
Script on open. So essentially, this method runs
immediately as soon as you open the spreadsheet. ARUN NAGARAJAN: That’s right. That’s a trigger in Apps
Script parlance. NICK JOHNSON: So this creates
the magical AdSense menu. ARUN NAGARAJAN: That’s great. And I also see that you are
programmatically building that array instead of populating
it in a big chunk. It’s much easier to
read this way. Everybody has a different way
to do that menu population, and it’s interesting to see
how you’ve done it. NICK JOHNSON: Yeah. For me, this corresponds more
closely to what actually pops up, but I guess that would be
different for everyone. ARUN NAGARAJAN: And you’re also
using separators to clean up your UI a little bit. I think that’s a trick that a
lot of folks may not know of. But if you pass in Null into a
menu object instead of a Name and Function Name property,
you’ll actually get a nice clean separator. And I’ve seen some people
actually use hyphens and dashes to clean up menus,
but we actually have a nice trick for that. NICK JOHNSON: I’ll go back
to the script now. ARUN NAGARAJAN: Now so far,
you’ve shown the API parameters to English literal
mapping and how the menus are set up. Where is some of the
magic happening? NICK JOHNSON: Just a little
bit more magic, Uninstall, which just triggers the menu to
appear should you install– ARUN NAGARAJAN: From
the Gallery, yeah. NICK JOHNSON: From
the Gallery. And then we have a whole bunch
of utility methods which create dates. And then these are the methods
which actually create the default reports. A couple of little total and
average things there. And actually, I’m going to do
this sort of upside down. ARUN NAGARAJAN: A lot
of code in there. NICK JOHNSON: It’s
a big script. ARUN NAGARAJAN: Magic
is not easy. NICK JOHNSON: Actually, I’ll
show you there right at the bottom, we have the Get
Help method here. ARUN NAGARAJAN: That’s a nice
trick as well for our viewers that may want to do something
that’s spreadsheet-based, and if you’re just distributing
all the content through a script, it’s a great way,
I think, to show Help. NICK JOHNSON: So this literally
just creates a new spreadsheet, uses the list which
you saw and map you saw right at the top, and populates
the spreadsheet using those. There’s nothing super clever
happening there. And then the actual magic, as
such, the bit that my manager would describe sneeringly as
magic, the bit that pulls out the column names is here,
and it’s fairly simple. We just iterate along the top
row of the spreadsheet using the data range and then look for
the right edge, which I’ll come back to. And then every value it finds,
it replaces spaces with underscores and converts it to
uppercase, which lets you choose your own capitalization
as long as it’s in about the right form for the– ARUN NAGARAJAN: So that
essentially normalizes the headers into something
that’s more systemic. NICK JOHNSON: And then we
attempt to find an entry in either the list of metrics or
dimensions up at the top, and if we find one, then we turn
that into a request, which is made to the API. And if we don’t, we do something
else, which I’ll just demonstrate. So if I type something which is
not a dimension or metric here and then refresh the
report, it turns red because that it– ARUN NAGARAJAN: Could you zoom
in a little bit, Nick? It’s actually kind
of cool, too. So that’s a nice way to not
block the user, but you just tell them that I did not
populate this column. NICK JOHNSON: Precisely. ARUN NAGARAJAN: Cool. That’s great. NICK JOHNSON: So I’ll go
back to the code now. And then having iterated through
those, we’ve already gathered through that what the
right hand edge of the actual column you just entered was,
so then we look along the right edge for the words Start
Date and End Date and use the cells to the right of those
to pull those numbers out. ARUN NAGARAJAN: And that could
have been a pop up. In this case, it’s
non-obtrusive to have it in a cell. NICK JOHNSON: Yeah, it was
more magic, essentially. ARUN NAGARAJAN: How
does it know? NICK JOHNSON: Magic Spreadsheet
was the code name if this had one. ARUN NAGARAJAN: That’s funny. NICK JOHNSON: And then if
I can actually find this in my own code. Why don’t I take advantage– ARUN NAGARAJAN: Of the
new search features. NICK JOHNSON: –of that. Find the wrong thing. ARUN NAGARAJAN: It’s also harder
to navigate at that zoom level that you have. One of the things, as Nick is
finding that, I’ll talk about is the reason some of this
data makes sense in spreadsheets is that
it gives you an immediate charting interface. So a lot of folks take this
tabular data and are able to build line graphs and pie charts
and other scatter plots that may make sense, based on
the data that they care for. So it’s actually interesting
that it’s not just the data you’re able to acquire, but
some intelligence from viewing that data. You could archive it, you
could have it published automatically to a Google site
or an internal portal. So there’s a lot of nice things
that you can do once you get it into a spreadsheet
or hook into it with Apps Script. NICK JOHNSON: And the final
bit of magic happens here where it drops the data it gets
from AdSense right back into the report. There’s a little bit of
cleverness here and there, but nothing really special. ARUN NAGARAJAN: Cool. So one thing that we failed to
mention earlier was the way you get access to the AdSense
API is by enabling external Google APIs. We’re not actually going to show
that panel because that contains the API key that we use
for this demo, but if you could just quickly show where
in the menu it is. And also, there’s a really good
tutorial that we have which walks you through a
lot of details on how do you set this up. In this case, it shows you how
the charts work as well. And this actually is a different
set of code and all about automation versus some
of the stuff that Nick is showing here, which is about
dynamically populating a spreadsheet with parameters that
are purely column header based, rather than you knowing
what the API is all about and all the details about it. So this spreadsheet you could
put in front of a business user that’s used to various
reporting solution, doesn’t know what API stands for, and
they’re able to use this. NICK JOHNSON: Yeah, the whole
idea behind this example was something simple that your
average, ordinary AdSense user could very easily just sit down,
plug data into, and have their data just pop up
in a spreadsheet. ARUN NAGARAJAN: That’s great. Can you pull up the tutorial
again that’s there today? Yeah, this is it. So in this tutorial, there are
a couple of sections that are really important. One is the getting an API key. So you need to go to what we
call the Developer Console. And it’s actually a really
straightforward process. The reason we need this is to
track the utilization of the API against your account, just
to make sure that no one person is disproportionately
using the API and affecting other users. So we can’t show you the console
because that reveals our API key, so you’d want to
track your own and keep it safe as well. But it’s actually really
straightforward. And if you just follow the
tutorial that we have today under our Tutorial section, that
will show you a lot of the details on how do you
get an API key and where do you paste it. AdSense is one of those special
Google APIs that you see under, I believe it’s
Tools, Use Google APIs. And you get a pop up, and you’re
able to toggle on the right APIs you care for. Once you do that, autocomplete
will now have AdSense dot, and then all the class names. What is probably the most
important AdSense class that someone needs to know
about, or method? NICK JOHNSON: Probably– ARUN NAGARAJAN: Let’s take a
look at the screen here again. NICK JOHNSON: If I just flip
back to the documentation. ARUN NAGARAJAN: The
documentation, that’s a great way to show that. NICK JOHNSON: I can actually
just talk a little bit about each of the concepts
we have here. ARUN NAGARAJAN: Sure. That will be really helpful. NICK JOHNSON: So I
mentioned before, Inventory and Reporting. So we’ve only really talked
about Reporting here, but something also important which
feeds into that is what we call Ads Inventory. So you have Ad Clients, which
are essentially the products you use. So if you have a blog, that’s
called AdSense For Content. You could also monetize a search
engine on your blog. This is AdSense For Search. There’s AdSense For Mobile
Search, AdSense For Video, AdSense For Games. There are a bunch of these. ARUN NAGARAJAN: So no
content left behind. NICK JOHNSON: No one. Below each of those, you have
a set of Ad Units, which are your actual ads. These are the server side
association to the actual code you put on your page. You have Custom Channels, which
you see here, which are essentially– I’ve heard them called Tracking
Codes or Labels. You might use these to organize categories on your site. So if you have a cooking
site, you might have pizza, pasta, haggis. ARUN NAGARAJAN: And those
are all the tags? NICK JOHNSON: Yeah. These are custom channels. And then lastly, you have URL
Channels, which are sort of what they sound like. They allow you to group your
content to different URLs. ARUN NAGARAJAN: Got it. And this mirrors the existing
AdSense Management API. NICK JOHNSON: Yes. ARUN NAGARAJAN: So if someone is
used to calling the AdSense Management API via REST,
then this will seem very natural to them. NICK JOHNSON: It’s almost
identical, and even more so, it mirrors the terminology
from the AdSense front end itself. ARUN NAGARAJAN: Great. NICK JOHNSON: So if you’re an
AdSense publisher and you use the front end, all these
terms should be reasonably familiar to you. And if you’re a developer, they
should be familiar to your users. ARUN NAGARAJAN: Got it. NICK JOHNSON: And then lastly,
you have the Reports Collection, which the Reports
Collection here and the Reports Generate Response. These are almost certainly the
most important and useful classes and objects
which are in this. ARUN NAGARAJAN: Excellent. NICK JOHNSON: So if I go to the
Reports Collection, you’ll see the Reports Generate Method,
the only method. ARUN NAGARAJAN: With really
good samples, too, I see. NICK JOHNSON: Yes. Silvano did a fantastic job
building the tutorials and samples for these. ARUN NAGARAJAN: Who’s now
at the Google+ Team. That’s great. So the reason that I wanted to
point this out is that this API, if you’re using AdSense
today using our REST APIs and you’re running it on App Engine
or your own local desktop, Apps Script provides
a cloud-based environment where you’re able to plug in
pure JavaScript and then automate that reporting
aspect of it. And it’s easy to get started. Go to, and
anyone with a Gmail account or Google Apps For Business account
can start this, and you’re able to very
easily get going. So definitely check out the
documentation, a lot of good details in there. Check out the tutorial that
Silvano wrote that talks about the automation aspect
of AdSense. And then we hope soon to share
this magic spreadsheet and all the code associated with it
so that it acts as a great resource for the community
out there. Anything that we missed? I think we covered all the
things that we wanted to. NICK JOHNSON: Yeah, I think we
covered all the major points. ARUN NAGARAJAN: Yeah, so we’re
super excited to have Nick and show all the cool stuff that
he’s built, this magic. And we hope to share the code
with you very soon, as soon as that’s ready and fit for
sharing, and as soon as there’s a way to publish
it as well. And we’re really excited to
see what the community can build with it. I think this, along with a lot
of different analytics, APIs that we have, you can actually
take spreadsheets and act as both input and output mechanism
for analytics information. Because it’s so data rich and
information heavy that showing it as a chart or showing it as
some other alerting based interface, I think is
really valuable. So thank you very much for
joining, and thank you Nick for joining us as well. NICK JOHNSON: Thank you very
much for having me. ARUN NAGARAJAN: Yep,
absolutely See you.

Leave a Reply

Your email address will not be published. Required fields are marked *