Integrating Google Apps with Salesforce using Google Apps Script

IKAI LAN: Hi, everybody. Welcome to a very special
edition of Google Developers Live. My name is Ikai Lan. ARUN NAGARAJAN: Arun
Nagarajan here. IKAI LAN: And today we’re going
to be talking about how to integrate Google Apps
with Salesforce using Google Apps Script. And when I say we, I mean Arun’s
going to be doing this and I’m going to be following
along just explaining things about Apps Script. Because I don’t know very much
about the Salesforce API at all, whereas Arun knows a lot. ARUN NAGARAJAN: All
right, yeah. So actually, one of the things
that I realized this morning was that the very first time
that I worked with was in 2006. Yeah, which is decades
ago in tech. And it was back when we were
building some mobile applications, and we had
to tap into the API. And all they had was a fairly
early-on version of their SOAP API. So I was very happy to
know that things have come a long way. It’s much more robust, much
more secure, very easy to connect to. And it’s always been
cloud-based. So it’s a great fit for Google
Apps in general to exchange data, documents,
spreadsheet-type data, things like that. And before we kind of dive into
the API aspect of things, maybe for yourself and the other
viewers that are out there, maybe I’ll just give a
very quick overview of what Salesforce is. IKAI LAN: What is Salesforce,
Arun? ARUN NAGARAJAN: Exactly, ask
me the question, right? IKAI LAN: What is it? ARUN NAGARAJAN:
is a CRM system. So there are many like it. Oracle, Microsoft– a lot of
these vendors have their own CRM systems that are
on the cloud. IKAI LAN: And a CRM
system is– ARUN NAGARAJAN: A CRM system
stands for Customer Relationship Management. It allows you to track contacts
with your customers– for sales, for service– and it allows you to track
accounts and contacts and opportunities against them. Opportunities are things
like deals. So you may want to know– I started to market
to this customer. It took them a couple weeks
to get to know what it is. Now they’re evaluating us
against another vendor. And then finally, they
procure our software and paid us our bills. So if you’re a small company,
a big company, this is a typical flow for a salesperson
to have. It’s a pretty common business
purpose software. So what I’ll do is just give you
a little overview of the components and the contents that
a CRM system may have. By the way, everything that
I’ll be doing today is something that you guys
can follow along. actually has
a free developer version. And it’s actually– I think it’s free unlimited
and allows you to have hundreds of records for testing
purposes and a lot of good things there. And obviously, it’s great
for testing as well. You can actually add your
content and get a good sense for what it’s like. So a typical CRM system, when
you come in, you have– the key moving pieces
are Accounts. So accounts are like
companies, right? So these are– if I just go
in, I can see all the different companies here. You have United Oil & Gas,
Express Logistics, GenePoint, Grand Hotels. Contacts are people that
work at accounts. If I just go into Contacts,
these are people. They have title, they have phone
numbers, they have email addresses, and so on. And Opportunities are what
I mentioned before. These are sort of the deals. So a single account can have
multiple opportunities associated with it. They could be for different
amounts, and they could be in different stages with different probabilities as well. So those are the three
main pieces that most folks will deal with. So that’s So what we’ll do is actually,
we’ll start off real easy. And we’ll actually kind of
create a spreadsheet to dump some data from So we’ll try to do three
things today– get data out of We will then try to push data
into And then we’ll try to do some
interesting workflow-type things where we tap into an
email and then upload data into
contextually. IKAI LAN: So to clarify a little
bit, what are some of the things that you can
do in And what would be some examples
of reasons that you’d want to get this into
Google Apps? ARUN NAGARAJAN: So there are a
couple of important things that I think people will end
up doing in One is– so if you’re a salesperson that
you’re on the road, you want to be able to quickly
look up these contacts. You want to be able
to log a meeting. You want to be able to say, I
met with this person on so and so date, the call went well,
things like that. Also, if you’re an internal
sales or marketing person, you’re oftentimes bulk-editing
data. So you want to load
data en masse. So you want to import it from
a lead spreadsheet and then put it into,
for instance. Or if you’re a sales manager, a
lot of times you want to run analytics on it. You want to be able
to look at– what are the different stages of
the opportunities that I’m tracking right now
and all sorts of interesting things like that. So those are the key use
cases that I see. And I’ll actually start with
the one that I mentioned, which is downloading
data en masse. So what I’ll do is I’ll
go ahead and– I’m in Drive, so And like I said, everything that
I’m doing here you should be able to follow along. Some of it might be
a little tricky. But what I’ll do is– in this, for the very first one,
we’re actually going to use a canned script. So someone has written a really
nice script that works really well with the Google
Apps Script platform. So I’ll go to the
Script gallery. IKAI LAN: And the script gallery
is– you can always submit something to the
script gallery. You can do this when you’re
in the script editor at Others Submit to Gallery. I think there’s a little bit of
confusion between Submit to Gallery and Publish. So Publish is what you do when
you want to expose your application as a web
service or web app. Whereas Publish to Gallery is to
publish it into the script gallery here, which you can
access right now through a spreadsheet. You go to Spreadsheet. You go to Tools and
Script Gallery. You can look for things that
people have published. ARUN NAGARAJAN: And I’ll
go ahead and– I just searched for
Salesforce– and try clicking on Install. And this is actually one of the
members that a contributor did, Sanjay Gidwani. I’m going to authorize
this script. Going to close– close it here. And you will notice that there’s
a new menu available now called Cloud Connector– great name for it. In this one, the way it’s going
to work is it’s going to actually ask me for
a password. So it’s using the old mechanism
to authenticate. It’s going to actually make me
type in my password, which obviously may not be the
most secure approach. Let me log in here. And then one of the things that
Salesforce instituted a few years ago was that, for a
third-party app to access– it’s sort of like an
application-specific password, except it’s for all
third-party apps. And I actually have to save
it– they email you it. And I’m going to save that. So that’s another reason this
approach is not the best– IKAI LAN: This is kind of a
really funny approach if you think about it. Because the whole thing about
using OAuth and third-party authentication is that you want
to give people a password that’s not the same password
they’re likely using somewhere else. So in this case, they’re asking
for your password, and they’re asking for
initial security. So they’re protecting their
system, but they’re not protecting you. ARUN NAGARAJAN: That’s
about it. That’s fair. So I’m going to log in. So it’s sort of stages
of things. I type in my credentials. I have to hit Login. And then I can query. So this is actually
interesting. So the Salesforce interface
is based on the syntax called SQQL– SQOL– IKAI LAN: So it’s like SOQL. ARUN NAGARAJAN: Exactly. It stands for Salesforce
Query Language– that’s a typo there. This is the right version, SOQL
And it looks like SQL. It’s got most of the
properties of SQL. But sort of like some of the
systems we have, it’s got some things that SQL cannot do–
things like Group By, some of the advanced structures. So what I’ll start off with is
some of the simple ones. So I can say Select ID,
FirstName, LastName. So this requires some
familiarity with the system, but then it’s actually
quite nice to use it. So I can say From Contact. I hit Query, and it’s going to
dump the data into a nice spreadsheet. So now I have it into a Google
spreadsheet, and I can go ahead and export it. I can save it, and share
it as a PDF. It’s in a spreadsheet, so I can
do a lot of cool things. And I can create another
tab of the sheet here. And what I’ll do here
is query for– let’s get account information. I’ll show a couple examples
here as to why this might be useful. So I’ll say Select ID,
Name from Account. IKAI LAN: I notice that
this script caches the credentials somewhere. ARUN NAGARAJAN: That’s right. IKAI LAN: So do you know
where it does that? ARUN NAGARAJAN: Yeah, it does
it under User Properties. I don’t want to show
it because my password will be there. And I’ll do– IKAI LAN: You almost fell
for my trick there. ARUN NAGARAJAN: So I’m
actually going to create a new tab first. And there’s a lot of benefits
to this approach. And I’ll show you the real
reason why this might be really beneficial for
a sales manager. I’ll say Select Name– let’s see– Amount, remembering my
Probability from Opportunity– “opp-or-tunity”– if I can spell. All right, so this is going to
download the sales deals. 100% means it’s sold. 60% means the salesperson thinks
it’s getting close. And then if it’s 20%, they just
started this opportunity. IKAI LAN: Is that probability
or progress? Well, I guess it depends on
how people want to use it. ARUN NAGARAJAN: Yeah, so the
field is called Probability, so let’s kind of go with that. So what I can do as
a sales manager– and this is just the
fact that it’s in– oop, [? can’t see. ?] All right, so let me actually
go back in, say Data, and create a pivot table. So what I can do very quickly
is add something as a pivot table and add values
for amount. And you can see that very
quickly, I have a little stage here. And I can add another field for
Amount, and this one I’ll do just Count. And you can see that, as a sales
manager, I can see that my sales pipeline
is left-heavy. So a lot of deals are stuck
in the early stages. And I can just go ahead very
quickly and then select these guys and insert a chart. And we’ll call this a typical
pipeline chart. And there it is. So as a sales manager, ignore
the ones that are 100%. That’s kind of skewing
the data. Just look at this part. I can see that a lot of these
deals are just starting up. So I cannot expect a great next
two weeks, because it takes a couple of weeks
for deals to move. So these are the types of data
and these are the types of reports that a sales manager
likes to see. So that hopefully was a little
example of why you want to bring it into a spreadsheet
and analyze– IKAI LAN: That’s a very
good example. And there’s one more thing you
could do here, which I think is kind of cool, is that, with
this chart– you can publish this chart. ARUN NAGARAJAN: That’s
right, that’s right. So you can actually publish it
to a site or a dashboard, And then that person doesn’t have
to have access to the interface. So this data is now published
and very similar to the analytics example a
couple weeks ago. IKAI LAN: And that’s interesting
because Salesforce also provides functionality
that allows you to create dashboards. And some people might
prefer that method. Some people prefer
this method. There are advantages and
disadvantages to both. If you use Salesforce natively,
it has native access to the data. Whereas when you’re using Google
Apps and Apps Script, you have to remember to import
the data and always get the newest data. But you also get access to other
Google properties such as– you could access Calendar,
Gmail, and, I don’t know, Finance. ARUN NAGARAJAN: Yeah, Sharing,
all the versioning, all that good stuff. And let’s actually go ahead and
take a quick look at the code as well. This gentleman did a real good
job here of calling the SOAP service for the login part, but
then followed it up with the ability to call the
Salesforce REST API. Sorry, my font’s a little
funky here. So you can see all the
menus that get added. And then let’s just take a quick
look– so here’s the login process. So that’s the SOAP message
that gets sent up. And then they follow it up with
the ability to call the REST data, which is through
a very simple function called Query. And you can see that that is a
really straightforward call. So this is the beauty
of Salesforce API or really any REST API. And one of the nice things that
you’ll see hopefully is that, anything that we’re
showing, hopefully you’ll be able to adapt conceptually and
also code-wise into any other system that you may have. IKAI LAN: Do me a favor and
scroll back up real quick to the XML. So a couple of things to point
out here are that, when we’re constructing the XML, it looks
like this developer has done so by building the
string directly. Another method you could do this
is using the XML Service. Now, I personally think that
for smaller XML documents, sometimes it’s just much easier
to do it this way with direct string. Because you don’t have
to remember, where in the DOM tree am I? You just go ahead, you construct
your XML, and you’re ready to go. ARUN NAGARAJAN: Right,
absolutely. That’s correct. IKAI LAN: The only thing to be
aware of is, when you do use tools like XML Service,
it takes care of escaping for you. So you have to remember–
which quotes am I using? Am I escaping or not escaping,
greater than, less than? Just be aware of these things
when you’re writing XML you want to send as part
of an API. ARUN NAGARAJAN: Right, so
if my login name had an apostrophe in it, that
system would break– which is valid. IKAI LAN: Oh yeah,
makes sense. ARUN NAGARAJAN: So hopefully,
that was interesting. And that’s a script that we
didn’t have to write. A great member of our community
wrote it, and it’s available today for
you to try out. What we’ll do next is actually
use the REST API and actually upload some data in bulk. And for this, we’ll actually
use OAuth. IKAI LAN: OK, OAuth, yeah. ARUN NAGARAJAN: Yes, this is
going to involve a little bit of copy-pasting of the code
I wrote ahead of time. IKAI LAN: So again, while he’s
doing this I’ll talk a little bit about OAuth. OAuth, if you don’t know what
it is– it’s a mechanism for authorizing third parties
to use your service. So a good description I’ve seen
on the OAuth website is that it’s like a valet
key for the internet. So for instance, if Arun has a
service and he calls it Arun’s Social Network. He’s got a total of maybe three
or four people that are using it, but I want to write
applications against this API. So me as a user– you don’t want to train your
users to give out their password to third-party APIs. Now, this is something that was
very common in Web 1.0. It started to become less
common in Web 2.0. And nowadays, everything,
whether it’s Facebook or Twitter, you get an
authentication panel. In your browser, users
get to see HTTPS Twitter, HTTPS Facebook. So you know they’re giving their
password to the right authority and not to ARUN NAGARAJAN: That’s right. And Salesforce actually does a
really good job of explaining this as well. They have a really good
resource here on their Developerforce wiki. And you should be able to just
search for it, and we’ll make sure to include a link
for this as well. It’s a really good overview of
all the cool things that the API can do, different
approaches. And you want to use the web
server flow when you’re dealing with Apps Script. And we will get into this
probably deeper into a blog post or a separate session
on this by itself. It’s a fairly complicated
topic. And it’s a pretty sophisticated
piece of documentation that they have
on how this all works. And we are the client
application in this case. OK, so what we’ll do is I’ll
have to– for this, I just created a script against
the spreadsheet. So I went to Tools, Create
Script Editor. I’m just going to create
a blank project. IKAI LAN: So he’s not going
straight to He’s creating a spreadsheet,
and he’s going to Tools, Script Editor. ARUN NAGARAJAN: That’s right. And a lot of the ideas
will apply even if I start from scratch. In this case, it’s a lot easier
for me to kind of showcase this in a spreadsheet
container. OK, so what I’ll do is–
actually, I wrote up all the code ahead of time, both
for sharing it and for me to copy and paste. So if you go to my GitHub
page, it’s all there. So you can follow
along as well. IKAI LAN: And what’s your
GitHub username? ARUN NAGARAJAN: Entaq– E-N-T-A-Q. IKAI LAN: Does that stand
for something? ARUN NAGARAJAN: No, it’s– don’t put me on the spot. IKAI LAN: Do you know what
my GitHub name is? It’s Ikai, I think. ARUN NAGARAJAN: You have
a very unique name. OK, so what we’re going to do
is I’m going to go ahead and copy some of this code. So the one called
RowUtilities– this one should look
really familiar. This one is just code right
from our documentation. This has all the utilities
around converting a sheet into a JavaScript object. It allows you to query for them
by range, and then all sorts of good stuff. So let’s go to the Raw
here, copy this, and then come in here. IKAI LAN: I’m sorry,
let’s go back. That was kind of on
the quick side. So when you’re using GitHub, if
you’re new at GitHub, one thing you’re doing when you look
at code is that you can copy-paste the code. But look for the Raw link. Because it’s sometimes easier to
click the Raw link and open up just the text of the code. So that’s what Arun is
doing right here. ARUN NAGARAJAN: That’s right. And when they show you the
preview, it’s really nice to get all the syntax highlighting
and comments and things like that. But when you want to copy
it, you don’t want that formatting. All right, so we
paste that in. And I’m going to save this
project as Salesforce Scripts. And I’m going to rename this
just to make sure I understand. So I’ll call this
RowUtilities. And then I’m going to
create a new script. OK, and we’ll call
this Salesforce. IKAI LAN: You’re creating
a new file. ARUN NAGARAJAN: I’m sorry,
yeah, that’s right. Thank you. And I’ll go back and then pick
this guy, the OAuth code. And I’ll call this guy– again,
doing it quickly. But all I’m doing is bringing
the code over so that I’m not typing this in front of you. And you can use this today. I should say that it’s not
production caliber. We’re working on cleaning that
up and publishing it as a sample at some point soon. But it’s great to get started
and get a sense for what the script does. IKAI LAN: I’m kind of already
seeing where you’re going with this when it has the
authorization page. I can’t wait because I can
already tell it’s going to be really ugly. ARUN NAGARAJAN: I said
not production ready. All right, cool. So we’ve got the stuff. So what we want to do now is
actually a couple of things. So I want to– in order for OAuth to work, your
script is both bound to a container and also published
as a web app. So that’s kind of a tricky
concept to get to make sure it makes sense to you. So it’s got two entry points,
one from the spreadsheet, another one when it gets called
in from the browser. So if you can think about that,
that’s necessary for the callback that the OAuth service returns with the token. IKAI LAN: Let’s try to
rephrase that again. So you’re saying that we
need two entry points. Because when you’re doing OAuth,
in a typical OAuth flow, after you go to,
say, your service,, and
you say, yes, grant access
to my personal info, it’s going to redirect
you back to my site. So that’s what he’s talking
about when he says he needs to expose a web app, which
he’s doing right now. Because inside this app, is going to pass a bunch of
authorization codes that you can then use to do some key
exchanges and make API calls on my behalf. Now again, the scope of OAuth
is big and can be very complicated. So we’re not going to
go too much into it. But that’s why he needs a web
interface for this even though he’s not going to be serving
a web application. ARUN NAGARAJAN: That’s right,
and hopefully you’ll be able to watch it again and get the
flow if we’re going too fast. But this should all make
sense in a little bit. OK, so now what I did was I
copied the URL that this service was published as. And what I’m going to do now
is go into Salesforce and register a new application. So this is me telling that it’s OK for this custom third-party
application to access my data. And the administrator
does this. So we go to Setup. We’ll go down to Develop. We’ll go down to
Remote Access. And I have a couple of samples,
but this is all– you want to do this from scratch. So you want to go
down to hit New. So this is the application
registration. Again, this is something that
a single administrator does for all their Salesforce
users. So we’ll call this Ikai Demo. This is a great live
demo, hopefully. And I’ve saved myself
a little logo. I’m going to put that in. And I’m going to just point
to our developer site if something goes wrong,
and then just use my email address there. And now here’s the
important bit. It wants a callback URL. So this is the callback URL that
the web app calls into, the redirect calls into. Oh, I lost it. IKAI LAN: Right, so again, this
is the page that he’s publishing. This is the page where
Salesforce is going to give back a token. This is how Salesforce
is going to give Apps Script a token. ARUN NAGARAJAN: So I’m going
to paste that in. Now, there’s one thing that
you have to tweak. So when a script is bound
to a container, it’s not It’s So this is a manual edit,
and hopefully we’ll correct this very soon. And then another thing that’s
really great about it is, as an administrator, you can give
domain-wide privileges. So you can make this application
available for all users without them having
to authorize it. All they need to do is log in
so that they don’t get that warning message saying, hey,
there’s a third-party application. Do you trust it? Which may alarm some people. So I won’t check it for
this case because it’s just me for now. So I’m going to hit Save. And what this is going to do
is you can see that it just registered this application. The application’s called
Ikai Demo. You have the callback URL
that I pasted in. And there are two bits of
information that are very critical here, the key
and the secret. So I’m going to reveal it. You can use it. I’ll delete this after. And this needs to get pasted
into the application. So copy this and– IKAI LAN: I know that some joker
out there is going to try to hack this account. But just so you guys know,
this is a test account. You’re going to get absolutely
nothing of any value out of this. ARUN NAGARAJAN: So what I’m
going to do is copy over the dummy one that I had saved in
there, delete it, paste it, and then also copy
over the secret. So this is a little
bit of a set up. There’s an overhead involved
with OAuth. But in the end, your
users are more– their data is more secure. They trust the applications
more. So it’s a really worthwhile
thing to do. So that’s what I’ve done so far,
the ID and the secret. So that’s the pairing that
explains that this code is Ikai’s demo application
that we registered. All right so, so far so good. And I’m going to quickly
test this as well. So for now, since I’m the
editor, I’ll just pop open this script directly. So I’ll just hit Run. That’ll make me authorize it. IKAI LAN: So this is a demo. But if this were a real script,
there are a lot of things that we could do to
enhance the user interface. Like, for instance, there’s the
bit about replacing the URL script in Docs. We could have made that a UI
that came up where we replace it– where we just go ahead
and do a string substring. But we’re not trying to
do that right now. We’re trying to show you
guys how to access Salesforce using apps. ARUN NAGARAJAN: Yeah, the goal
is to give you guys the sample code, a bunch of recipes, and
the building blocks with which you can go ahead and build
the greatest Salesforce integration we’ve ever seen. So I’m going to click on just
one option here called Upload to SalesForce. And actually, one thing that
I’ll want to do is clean some things up here. So ignore this for a second. Ignore that. IKAI LAN: Was that your
password out there? ARUN NAGARAJAN: No. Oh, was it in there? IKAI LAN: I don’t know. We’re changing it
after the show. ARUN NAGARAJAN: I’m going to hit
Run salesforceEntryPoint, hit the button. And what that’s going to do is
say, hey, this is the first time you’re calling this. I don’t know who you are. I tried to log into
Salesforce. It wanted me to go in
and then hit Login. So I’m going to hit Click
Here to Start. IKAI LAN: It’s opening
a new tab. ARUN NAGARAJAN: So now you can
see that it says, Ikai Demo is requesting permission to access
your information. You can see the logo that we
supplied, the description, and all sorts of useful things that
I think is important. And I’m already logged in. That’s why it didn’t
ask me to log in. If I weren’t logged in, it’ll
actually make me log in again. So now I’m going to go
ahead and say Allow. And now you can see that
the URL callback– client identifier invalid. I screwed something up. Let’s take a look. Let’s try one more time. IKAI LAN: Ah, your client ID. Now, you pasted over the client
secret as well as the key, but I don’t think you
set the client ID. ARUN NAGARAJAN: No, what I
didn’t do is, once I pasted in the code, I didn’t
republish it. IKAI LAN: Ah, of course. ARUN NAGARAJAN: So this is one
of the things that– you kind of have to publish it first,
and then you’ll get the new credentials. And you have to paste it in
and then republish it– a little bit of an annoyance. All right, go ahead. IKAI LAN: So this
is OAuth 2.0. In OAuth 1.0, they had all these
different parameters you had to pass in. And one of them, I believe,
was a client ID. So I don’t think you have
to do that anymore in OAuth 2.0, but– there you go. ARUN NAGARAJAN: Cool, so
I just refreshed it. Hopefully the error message will
not happen when you’re doing this for the first time. But you can see that, at
this point, the URL is [? rpublishservice ?]. So this is the callback that
I was talking about. OK, so I can close this window,
go back in here into the spreadsheet– again, so
this is not very nice. You could have a little
timer in here. You could automatically
close it. I was being a little lazy. So actually, before I use it,
let’s set up the context as to why we want to do this. So if I go into my Account
section here– or Contacts, I should say– I have a lot of contacts here. But maybe I want to bulk-enter
a bunch of contacts. I’m an admin, I’m familiar with
spreadsheets, I don’t want to deal with
directly. So what I’ll do is just type in
a bunch of contacts, type in some numbers– do you want to give your
real email out, Ikai? IKAI LAN: Yeah, that’s fine. How did you know? ARUN NAGARAJAN: Let
me fix this. I think there’s some
format checkers. I think it makes sure that
it’s at least numbers. And we’ll use our producer,
Eric, as well. [email protected] All right, so we’ve just punched
in three contacts really quickly– their names,
their contact info. What I’m going to do is go
ahead and say Upload to SalesForce. And I’ll show you the code as
well, so that this doesn’t seem like magic. And this time, it again says,
hey, you know what? You don’t have to log
in this time. I already have the token. And I’ve gone ahead and
uploaded these people. Beautiful, isn’t it? IKAI LAN: I’m, like, laughing
at this right now. You made the message the
giant title box. ARUN NAGARAJAN: Of
course, man. So what I’ll do is actually
go into New This Week, and there they are. So the contacts that
we just added– hopefully you guys can see
on the line there. But that’s the power
of Apps Script. So you can work with the simple
tools that you’re familiar with, things like Spreadsheets, things like Docs. And then through a
couple clicks– boom, publish out to
another service. IKAI LAN: And there are lots of
reasons why you might want to use the Spreadsheets
UI to do this. Now, could we go ahead and show
us how you would create a new contact using the
Salesforce UI? ARUN NAGARAJAN: Yes,
New Contact. IKAI LAN: So we’re going
to click New Contact. And now you’ll see that, OK,
you’ve got to jump around from field to field. So you get a lot more features
here for instance, but you could always modify the script
to include additional fields. It’s just that suppose that–
he very quickly entered in three contacts. And now it would probably– here you go. It’s taking a little bit
longer to do it. ARUN NAGARAJAN: And then
I would have to do it again, yeah. IKAI LAN: And he’d have to
do it again and again. So there are definitely
lots of reasons. Other reasons are, for instance,
maybe you have a Google Form attached to your
spreadsheet where people submit their contact
information. ARUN NAGARAJAN: We’re not going
to show that right now. But for instance, you could
just go and say Tools, Create a Form. And it’s smart enough to know
that the fields are FirstName, LastName, Email, and Phone. And this is a very simple thing
you can publish out. And yeah, that’s great. So I’ll show you the
code real quick. We won’t dwell on it too much
because there are a lot of moving pieces. But what it really does is calls
the Utilities function getRowsData– hopefully you guys
can see that– and then just iterates through
all the objects, creates a little post payload, and
then just sends it up using URL Fetch. So URL Fetch is your friend
in these cases. And then further down– and you
can see this in the script as well– there’s lot of set
up that needs to happen to preserve the user’s properties
for the token, for the callback server for
themselves. You need to make sure that the
authorization header is passed where the OAuth token that was
downloaded is passed up. So these are the things that you
need to keep in mind, but hopefully all the samples
will make it easier for you get started. Does that make sense to you? IKAI LAN: Makes sense. ARUN NAGARAJAN: All
right, cool. So let’s do the last bit, and
then hopefully there are some questions as well that
we can take. Great, so the last thing
that I wanted to do was let’s go into– so I set up this sample account
called Arun DevRel. All right, so this account– he’s a COO. And he’s got a bunch of details
about him– the last time I talked to him, the last
time I tried to sell to him, and whatnot. So this is a contact that I have
a relationship with for business purposes. IKAI LAN: So I’m thinking
about this demo. And we should have named it
[? Colleague ?] because now it’s just going to
be confusing. Do you know what I mean? ARUN NAGARAJAN: Yeah,
probably. There are a lot of– yeah, I should probably have
thought about that. You’re right. So what we’re going to do for
this demo is we’re actually going to paste in a new
function, which is going to allow the Apps Script
environment to go ahead and seek into my inbox and
find matches for emails from this person. And then actually if there’s an
attachment associated with it, take that attachment and
push it up to Salesforce as an attachment that’s available
within the system. So that when I’m collaborating
with someone through, they can
reach into this. They can edit it. They can add notes
to it, and so on. IKAI LAN: So again, to
disambiguate the accounts, he’s receiving emails
from Arun DevRel, and he is Arun Nagarajan. ARUN NAGARAJAN: That’s right,
and I’ll actually showcase that one more time. So the email associated here
is [email protected] So that’s my DevRel account– Developer Relations, code for. And I’m actually logged in to
this account, so this is– you can see that, it’s
Arun DevRel. And this is my inbox associated
with Salesforce. So this is just my test inbox. You see there’s a few
messages in there. OK, so what I’m going to do is
pretend to be this customer of Arun and hit Compose. And I’m going to say,
my details for you. Sending this doc for review. And I’m going to actually– I think it’s called Sales Detail
or something like that. And it’s actually a Microsoft
Word documents as well. IKAI LAN: So a lot of reasons
why Salesforce allows attachments is that a lot of
times, you have these things called RFPs, which stands for
Request For Proposal. So typically, people tend
to use Word or PDFs. Now as much as I would love to
see people doing this in Google Docs, the reality is that
there are lots of times when you want to
send out a PDF. Or perhaps you want to send
out a Visio doc that says, here’s how you would wire
up your network. ARUN NAGARAJAN: Right, there
are a lot of people that haven’t seen the light, I
guess, with Google Docs. So what I’ll do is I’ll paste
in this other function. And this is called scanEmail. And for now, we’re just going
to run it manually. But this, as you can see after
this runs, can be triggered every hour or every minute,
whatever makes sense for your personal purposes. I’m going to save this. And I’m going to go ahead and
pick that function to run. And I’m going to say– so it’s going to realize that
I’m now trying to trick it and read my email. So it’s going to make sure
that I reauthorize it– Grant Access, Close, this
time run it for real. All right, so what
is it doing? So it’s going into this inbox,
looks through my email, sees that there’s an email from this
particular email address, which matches a Contacts email
address in Salesforce. And then, if the demo gods are
with me, there should be a new attachment that wasn’t there
before now visible. And it’s in fact also converted
into a PDF. So I can click on
it and view it. IKAI LAN: Wow, very nice. ARUN NAGARAJAN: Make sense? Yeah, so let me just show
you the code real quick. So for the purposes of this
demo, I made it just so that it scans the last
five threads. You could go and be a bit more
intelligent, remember where it left off and do some pretty
interesting backoff there. IKAI LAN: So when I’ve been
programming with Gmail App, one thing that I like to do is
look for a document that looks for saved searches. Because there are a lot of very
powerful things you can do with Gmail searches. You can say, label dash label. You could say, has
colon attachment. There are lots of things you
could do to filter down before you start iterating
over things. ARUN NAGARAJAN: That’s
a good one, yeah. IKAI LAN: Since this is a demo,
we’re just going to go over the last five emails. ARUN NAGARAJAN: Exactly,
I’m cheating. So what I’m going to do is then
also look for emails with an attachment. I probably should have
used your trick. That’s a really good one. Then I’m going to find
the first attachment. And then I’m going to read
the attachment as a PDF. So this is that sort of online
conversion that Apps Script provides that I think
is ultra-powerful. And then I’m going to take
the bytes and then Base64 encode it. So this is something that the
Salesforce API stipulates. It only likes documents as
Base64 encoded blobs. And then it gets the email
address of the person. The address comes in this
format, so there’s a little regular expression magic
that happens. Then it sees, is that email
address associated with the contact in Salesforce? Runs that– IKAI LAN: And you’re using– that’s not SQL, that’s SOQL. ARUN NAGARAJAN: Then it’s going
to use the original name and attach a PDF extension to
it along with some details, give it a parent ID of that
person that matches that email address, and then just run it. IKAI LAN: And the parent ID,
what does that represent? ARUN NAGARAJAN: In this
case, the contact ID. So it runs the Select against
that entity, gets the contact ID like so, and then it
associates it with the parent. So most objects in Salesforce
can have attachments. So I can have a parent ID of an
account ID, and that’ll be an attachment against
the account. So hopefully that made sense. So what we’ve been able to do is
automatically find an email with an attachment in it for
people that have a record in my instance, and
then be able to send an attachment to that record. So this code– again, all of this
is on GitHub. Go check it out– pretty
easy to find. If you just go to, it’s the second project there,
hopefully. There it is. And then if you just look
through it, all the code we talked about is there. And then you’ll be able to
create a sample account in yourselves and
try this all yourself. IKAI LAN: Very, very cool. In fact, I think that– we
keep talking about SOQL, “so-quill,” and I think that
you’re so cool for being able to pull this demo off. I’ve been waiting for
that one all show. You’ve gotta give this to me. ARUN NAGARAJAN: No,
that’s good. IKAI LAN: Let’s take a look to
see if there any questions in Google Moderator. There weren’t any when we
started up the show. But it’s possible that we have
live viewers that, as watching, came up
with questions. So we’ll see if there’s
anything there. ARUN NAGARAJAN: I actually
don’t have a link to that directly, so let me see
if I can find it. IKAI LAN: So–– so if you’re finding this show
from our YouTube channel at,
you can always find out about future live shows at It’s going to take
a little bit. There we go. ARUN NAGARAJAN: All
right, let’s see. So “could you write an Apps
Script to integrate with Salesforce Chatter, Salesforce’s
social media channel for the enterprise? That would be neat.” This
is Marc R from Waltham. So the answer is, yes. So the Chatter API is also
exposed to the REST interface. And you’re able to query against
it and run clouds about which people are chatting
the most, which people are liking my posts,
and things like that. So the social enterprise can
be brought into the Google Docs platform as well– Google Apps platform,
I should say. IKAI LAN: Good question. ARUN NAGARAJAN: Really
good question. All right, let’s see. “You’re
probably already planning to do this. Please include a few minutes
of Goo Salesforce for those who are not using it.” IKAI LAN: For Goo Salesforce. ARUN NAGARAJAN: I’m not sure
what that is, but I’ll have to look into that. Hopefully, that’s a typo. But maybe they were just
talking about just an introduction to Salesforce. So another resource that
I didn’t point out is this REST API. So take a look at this
one as well. So
has all these docs. And this kind of outlines all
the real API endpoints that they expose– by objects, by version. So you can actually go ahead
and take a look at how we figured out the attachment
example. So I can just go in here, go
into Working with Records, Creating a Record. So this is the example that I
used to figure out how to upload a contact,
for instance– very straightforward. Cool. Does that make sense to you? IKAI LAN: It makes
sense to me. Let’s see if he’s come in
with any more questions. Because sometimes what happens
is, when we’re doing these live shows, more and more
questions come in. And if not, you guys know where
to find us and you can feel free to post comments on
the YouTube video as well. We’re going to take a look at
this every once in a while and see if anyone has any new
questions or comments. Again, you can always find us at
the standard channels, too. So all right. Is there anything else
you want to show us? ARUN NAGARAJAN: Let’s see. I think that that about covers
everything that I want to talk about. Hopefully this gives you some
ideas, some sort of building blocks with which you can take
and build your own cool applications and workflows. Think about integrating with
the Google Calendar. Think about doing some
interesting things with Gmail Labels and [? Start ?] Emails and things like that. Do you have any other ideas that
would make sense in this? IKAI LAN: Yeah, a very common
thing that we used to do is, sometimes you talk to people,
you talk to prospects, and they’re not ready to buy yet. They don’t have budget yet. But they tell you, I’m going
to have budge in Q3 or Q4. Now, Salesforce does have an
interface that says, find me these people when I should
contact them. But if you’re already using
Google Calendar, that’s another very interesting
use case. ARUN NAGARAJAN: Yep, setting up
reminders and things like that, that’s really cool. IKAI LAN: Right, and there’s so
many things that you could do once you integrate
with Google Apps. You’re taking all the power of
Salesforce, which you’re likely already using if you’re
a Salesforce shop, and you’re just extending it. Because a lot of times I see
people that are doing these very manual things. ARUN NAGARAJAN: Right, yes. Any time you find yourself doing
something that’s mundane or repeatable, find a way to
write a script for that. IKAI LAN: Right. OK, so I think that’s all we
have for today then, isn’t it? ARUN NAGARAJAN: Yeah, that
covered all the three things that I wanted to talk about. Thank you for watching. IKAI LAN: Thank you
guys for watching. Again, we’ll be having these
shows of various topics. And this is for people that
are on the live show. We’ll be doing a show
about triggers. This will be happening
next Tuesday. So check, and you’ll be able to see when
that show’s going to be. So if you were watching this
show and you’re thinking, well, this is all very cool,
but I want to periodically update a spreadsheet, or
I want to periodically send an email out. Now, I’m pretty sure you can
do that in Salesforce. But suppose you prefer
programmatically editing it and sending it out in Gmail. Well, you can do that
using triggers. And we’ll be talking about
how to use triggers. And that’ll be happening
next Tuesday. So again, go to And you’ll be able to find
details about future shows. OK, so yeah, that’s our show. ARUN NAGARAJAN: Thank
you very much. IKAI LAN: Thank you
guys very much. You guys have a great weekend. And we’ll see you next time.

4 thoughts on “Integrating Google Apps with Salesforce using Google Apps Script

  1. Hey, you know what else is cool about Salesforce and Gmail? They integrate EASILY with Cirrus Insight. You can log emails, create new records, and do a metric ton with Salesforce without ever having to leave your inbox. Oh, and there's a free trial. Check it out at!

Leave a Reply

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