for example i grabbed this json placeholder something some
website as you can see here if you search json placeholder you should be able to find this
it's basically just some json so if i for example click on this posts it should give us this json
response as you can see it's an array of posts so i want to basically pull this
data and put it in my spreadsheet and i'm just going to copy this link for now and i'll go to
my spreadsheet and i need some scripts to
do so so i'm going to go under extensions and do apps script
so i'll just quickly just paste that link here as a comment so we need to pull some data from
that so i'll just store this in a variable so i'll do const url equals and we'll basically just have
this link as our url so now we should be able to grab data from that url by using a url fetch app
and this has this fetch method and see what it's going to ask me for is the url so i'm just going
to type that url here like so and we can go to the beginning here and just basically store this
in some sort of constant maybe we can just right away call maybe not
let's just call it res
or response and yeah let's just do that so we'll take that and pass that url here
so at this point we got our response and that res is going to have some methods on it
one of which is going to be get content text like this
so what i'll do i'll store that in a variable too so i'll just call it const data i guess or data as
text let's call it like that because it's really just going to be text at this point
so if i wanted to see what this looks like right now i could do some console log
and basically just get this data as text thing here
so let's just rename this i'm just going to call it get my json data or something so
right now let's just run this and see what happens i'm gonna just name this project
so if i run this function get my json data right here so you can see what happens i'm
gonna go ahead and hit run it will probably ask me for some permissions and stuff which is fine
there it is so once i've run that see i get this response
right here which is basically the same thing i used to see here when i went to the json page
now in this particular json response if you look all the way on top left here it's a square
bracket which means it's basically an array of things in there instead of just one response
so basically this is going to give us an array and in that array we have this objects in our response
so what that means is that now i can work with this but you want to remember that at the moment
this is just text so we need to convert it to an actual javascript object so we can work with this
and the way we can do that we can basically just create another variable here and we'll do this
json.parse method and what this is going to do it's going to take that text and it's going to
convert it to an actual javascript object at this point so this data now instead of being just text
is going to be javascript object which we can think about it as like an array
of some sort so just to show you what the data is i'm just going to add a breakpoint here i'm going
to click in this line 12 so we can look inside of that data so you can see what's going on
alright so i'm going to save this and i'm going to click on this debug icon right here
and you can see it's going to basically run this code until
it gets to this line where i clicked in this breakpoint and it's going to stop
and you can see here now we can explore what we got and you can see if i look at this data thing
it's an array of 100 things at the moment and if i open this it gives me all the rows from that array
so you can see if i go inside of that data zero it will give me this where we have basically this
user id id title and body and then we have this that gives me the next
user id id title body which is basically if i just go back to this it's like the first object
has user id id title body and then the second object has those same things user id id title
body and so on so that's what we're dealing with right now with this particular response at least
and each one of these if you look here it's wrapped in this bracket so that's basically an
object so what that means right now is that since this is an array this data right here because
right now i'm looking at this data see right here because it's a data array we can actually run all
javascript array methods one of which would be the map method which would be really useful
in our case which will allow us basically let me just stop this so we can write some code
i'm gonna do map and this is going to allow me to basically run through that
list of stuff that array here and do something with it so this allows us to also save our
data whatever we decide to do with this to another variable so i'll just call it results i guess
like so and right here in this map i'm going to create a variable r and we'll do this arrow
function like this and the way you want to think about this at this moment is that
this is going to loop through every line in that data and every time this r here is gonna be this
so it's gonna be first time this then the next time it's gonna be this then the next time it's
gonna be this so that's r now i can call it something else like in this particular case
those was like posts or some website so instead of calling it r i could for example call it post
which would probably make more sense i usually like to call this r because r is kind of like
a row for me so this point when we look at this post it's really this now within that post now
we can decide what we actually want out of it so let's say i want the title and i want the body
of that post or maybe you want id title and body those three things that's fine so what i would do
i would come in here and return an array which is basically a row in that row i'm
going to comma separate things that i want to be returned so from that post i have for example id
and then from that same post which is that same object now i can bunch it basically return a
bunch of things so i have this id for example i can return this title i can return that body
so i'll do title i'll do body and i think that's enough
so basically in a single row
i want to get these three things so what i can do at this point i can basically just do something
with those results and all i'm going to do right now i'm just going to return those results
like so and save
once i've returned those results now i want to somehow get this information to my spreadsheet
right so what i'll do i'll just grab this
save this whole thing go to my spreadsheet and basically just do equals and do that
let's hit enter and see what happens and you can see how i got that response now in my spreadsheet
which is basically grabbing that data from there and pulling it over here and this is
like an active connection now so that would be the gist of it i guess so mainly the parts you want to
take away from this is that you basically want to use this url fetch app with fetch run this and
grab that data from whatever url you're trying to get this data from and the rest is really kind of
dependent on what your data looks like so things would change starting from like in here depending
on what you need you're gonna have to deal with it if you don't want it to be a live connection you
can also do that so for example if i didn't want this to be a live connection like this
and i wanted to just basically run this when i wanted to pull that data then instead of doing
this in my spreadsheet i could just go back to this and just write it to my spreadsheet
so instead of returning this results which was which allows me to do that pull directly to my
spreadsheet i would i'm gonna comment this so it doesn't run so what we can do we can write it to
the spreadsheet so i can get that spreadsheet so i'm going to do a get active spreadsheet
and i'll just store this in a variable
and then we want to get the actual worksheet we want to write to so you could get this
different ways so if you want to write it to your active sheet currently you could do this
or if you want to write it to a particular worksheet you can do get sheet by name
and then basically put the name here in both like in this case it would be sheet1
and then we can write to range so we can do something like ws get range
and then we need to set where we want to write this so i'm
going to start from the first row first column
and then the number of rows so you can get that number of
rows by getting the number of results here so i'm going to do results.length
and then number of columns well it's going to be three columns here so
you could do that or you can do results get the first row and get the length out of it
and then finally we can set our values to be that array which is that results here
so this way now if i run this let me actually remove this breakpoint instead of that
function that i've used here we can just have this
and we can run this whenever we want so if i go ahead and run this
i'm gonna do more permissions now because it needs my spreadsheet
so hopefully that already ran so there you go see now it's just static data
so you can come back later on and run this again and it's gonna re-run it and get your data again
if there was another object in the body here over here you would just do dot after body
and let's say in the body you have the person who wrote the article like something uh actually
let's stick with this other notation since we did that so you would do something like this
and then you could do let's say in a body there is something else here you would do another
paste and then whatever that other part is so let's say
there is a url or something that's the way you would have done that