Up next in 10
Google Sheets Web App Importing Live Data from API Using Google Apps Script Example
Dec 21, 2025
Buy the full source code of application here:
https://procodestore.com/index.php/product/google-sheets-web-app-importing-live-data-from-api-using-google-apps-script-example/
Show More Show Less View Video Transcript
0:01
Uh hello guys, today in this tutorial I
0:02
will be telling you that how to import
0:04
live data from internet and store it
0:06
inside your Google spreadsheet. So this
0:08
is a very basic example that I will be
0:10
telling you that how to call an external
0:12
API inside your Google app script code
0:15
and for the API I'm using random user
0:18
me. So this is a website. If you go to
0:20
Google and type here random user API and
0:23
the very first link which comes in just
0:24
click this link and [snorts] it will be
0:26
giving you random user data.
0:29
If you just go to this link random
0:31
user.mme/ API/ then if you click it then
0:35
a JSON response will come in. So
0:37
basically it is a results array and it
0:40
will contain the random information
0:42
about the person. So I will be printing
0:44
out the email address, the username,
0:46
password and a picture. So picture will
0:48
include the full thumbnail. You can see
0:50
that if I click this this is a thumbnail
0:52
of the person. Now we we will be storing
0:55
these information inside of a Google
0:57
spreadsheet. So you can see that this is
0:58
a picture. If I again click this, this
1:00
will be redirecting me to the person
1:02
image. And now in order to build this
1:04
first of all we need to go to uh Google
1:07
spreadsheet. Go to Google sheet and here
1:10
you need to create a blank sheet first
1:12
of all. So click on go to Google sheet
1:16
and click on blank.
1:20
So it will create a blank sheet for you.
1:22
And now in order to uh write code we
1:25
will be clicking on tools section and
1:27
click on script editor. So basically it
1:30
will be redirecting you to the script
1:31
editor page where we can write the
1:33
JavaScript code in order to accomplish
1:35
this task. So the very first link which
1:37
very first thing which we will do here
1:39
is that we will be making a URL variable
1:41
and inside this we will be copy pasting
1:44
the API URL. This is the API that we
1:47
will be hitting. And now in order to get
1:50
the response we will be using the URL
1:53
fetch class which is there inside this
1:56
URL fetch app class. And there is a
1:59
fetch method inside this. We will pass
2:01
the URL. And now we will be getting a
2:03
response. In order to get this response
2:06
in a string format we will be invoking a
2:08
method response dot get content text.
2:12
And now we just need to also parse this.
2:14
So we will be using a JSON do.parse
2:16
parse method and passing the this data.
2:20
That's it. So this will print out this
2:22
JSON response. Let me just rename it to
2:24
result. And uh
2:27
now I can just log it logger.log result.
2:31
And now in order to run this, click on
2:33
the run button. Basically first of all
2:35
just
2:37
uh name your project. Click on okay. And
2:40
now it will again ask for the
2:42
permissions if you execute for the very
2:44
first time. So click on review
2:46
permissions and I will be signing onto
2:49
my account and uh click on the advance
2:54
option and go to fetch app. So just
2:57
click on allow permissions.
3:00
So now if I it will run this function.
3:03
So just wait and now if I go to the log
3:06
section you will see that it has printed
3:08
out this JSON response that you can even
3:10
see inside this. You can see that same
3:12
JSON response it has printed out. And
3:14
now in order to print out the specific
3:16
fields that we are targeting email,
3:18
username, password, and the picture we
3:20
can just do a simple here result dot
3:25
results array because this is a array.
3:27
So we will be printing out the very
3:29
first element and we will be printing
3:31
out the email. Similarly, we can do that
3:34
logger.log log result dot results
3:38
and we can just go to the login object
3:41
and print out the username. In order for
3:44
the password this is very easy result
3:46
dot results
3:49
array dot login dot password and for the
3:53
lastly for the image we can just do
3:55
logger.log
3:57
result dot results
4:01
and it is there inside the picture
4:02
object. So dotp picture dotl large and
4:06
now if I run this it will print out all
4:08
these four things in the log it is just
4:10
executing it. If I check the log section
4:12
here you will see that first is the
4:14
email address username password and this
4:16
is the full URL of the image and now we
4:19
just need to store these details inside
4:21
of a Google spreadsheet. So first of all
4:23
we will be getting a reference to our
4:24
active spreadsheet which has opened. So
4:26
we will be declaring a sheet variable
4:29
and we will be using spreadsheet
4:31
app dot get active sheet
4:36
and after getting the reference we will
4:38
be
4:40
first of all be creating a header row
4:41
which will be including all the columns.
4:43
First column will be email. Second will
4:46
be username.
4:49
Third will be password
4:53
and the fourth will be picture.
4:59
And now we just need to append this row.
5:01
So append row function we will use and
5:03
pass this header row
5:06
variable. That's it. And now if I run
5:08
this, it will be adding this row inside
5:10
our Google spreadsheet. But we need to
5:12
grant permissions because we are using
5:14
spreadsheet for the very first time in
5:16
our project. So we will be signing in
5:18
onto a Google account. And click on the
5:20
advanced again. Click on this. And we
5:24
need to allow the permissions. So click
5:25
on the allow button. And now it will be
5:28
running our function once again. So you
5:31
can see that it has successfully run
5:33
this function. Now if I check the
5:35
spreadsheet you will see that email,
5:38
username,
5:40
password and picture. So these four
5:42
columns have been successfully inserted.
5:45
Now we just need to uh store these
5:49
details inside their respective
5:50
variables. So let me just uh declare the
5:53
email variable is equal to like this and
5:59
username like this
6:02
and uh password like this
6:07
and uh lastly for the picture which will
6:09
be picture like this and let me just
6:13
remove these extra parenthesis also.
6:18
And now in order to insert this value we
6:20
will again be using our uh method. We
6:24
will be declaring a row and inside this
6:26
square brackets we will be writing
6:28
email, username, password and picture.
6:34
And we will be inserting this by
6:36
sheet.append row again and passing this
6:39
row. And now if I run this it will be
6:43
storing these details.
6:46
You can see that. And now there is a
6:48
problem guys. It has inserted these
6:50
columns once again. So now in in order
6:52
in order for to remove this we need to
6:55
first of all clear out the spreadsheet
6:57
for the very first time when we just
6:59
invoke this method sheet. So it will be
7:02
clearing out the contents from the sheet
7:04
when we uh insert record. So if I run
7:07
this once again now you will see that
7:12
this these are the columns and this is
7:14
the random data which is inserted inside
7:17
of a Google spreadsheet. And if I click
7:19
this link here I will be redirected to
7:21
the person image here. And now in order
7:24
to insert 10 records at a time I can
7:27
just uh add a condition here to the API
7:30
question mark results is equal to 10. So
7:33
here you can just provide as many
7:34
results as you can. I can even provide
7:36
100 also. So it will print out 100
7:38
records at one time. You can see that it
7:41
will take some time. So again this is
7:44
this is very easy.
7:46
We just need to have a for loop. So
7:51
we just need to delete this row here and
7:54
we we need to have a for loop. So we
7:57
will be declaring an index variable I is
7:59
equal to zero. I less than results dot
8:03
result.length
8:05
because uh inside our let me just show
8:08
you we have this result sorry this is
8:11
result not results and inside this
8:14
results results is a array you can see
8:16
that results is a array so we are just
8:19
getting the length of the array
8:20
dotlength and we will be incrementing
8:23
the value of i each time the loop
8:25
progresses. So inside this we just need
8:27
to declare our
8:30
row that needs to be inserted and inside
8:32
this we will be printing out the same
8:35
things that we haveed here. So I can
8:37
just copy paste this from here.
8:44
So I can just here
8:47
instead of zero I can be printing out I
8:49
here. That's it. So this is the index
8:51
variable. And now same thing goes for
8:55
the username and password also. So I can
8:58
just copy paste it here from here.
9:06
I need to print out I here not zero. So
9:10
same thing for the password also
9:13
I. And lastly for the picture it is very
9:16
easy.
9:18
This will be result dot results I dot
9:23
picture.
9:25
After con constructing the row, we just
9:27
need to append the row. Sheet.append
9:30
row. And we will be passing our row.
9:32
That's it. And now if I run this guys,
9:35
it will be inserting 10 records at a
9:38
time.
9:41
Uh I have made a mistake here. What is
9:44
uh this mistake?
9:46
Uh row sheet.clear Clear.
9:52
Uh let me just see guys what is the
9:54
error here. I ++
10:06
uh sheet dot sheet variable is not. Let
10:09
me just first of all get the like do
10:12
this like this. spreadsheet
10:14
app dot get active sheet
10:17
dot append row and inside this we will
10:22
pass our row which is there
10:27
and now if I run this hopefully it will
10:29
be inserting the records
10:31
it is running
10:36
So
10:42
this is not done. So let me just see
10:45
what is the problem here.
10:48
Let me delete all this here.
10:52
Now run this.
10:59
So you can see that it is inserting but
11:01
it is not inserting 10 records at a time
11:03
here. So, oh sorry guys, I have not
11:07
changed this URL. So, that is my problem
11:09
here. So, you just need to change the
11:11
URL. I have not changed it. So, results
11:13
is equal to 10. So, this needs to be
11:16
changed. I have not changed this. So,
11:18
that is why the error was taking place.
11:20
And now if I run this.
11:26
So you will see that guys it has uh
11:28
inserted these records here inside our
11:31
you can see that 10 records are inserted
11:33
1 2 3 4 5 6 and all this have their
11:37
images here.
11:40
So you can see that. So now in this case
11:42
guys we have built a complete
11:43
application. we are importing data
11:48
and also we can just print out dotl
11:50
large here in order to get the full URL.
11:53
So dotl large and now you will see the
11:56
full URL. So just make this change here
12:02
and now you will see that guys. So this
12:04
is a complete example guys. If you click
12:06
this you will be redirected to the
12:08
person image. So thanks very much guys
12:10
for watching this video. If you like
12:11
this video then please hit the like
12:13
button, subscribe the channel and also
12:14
don't forget to subscribe.