API Tutorial For Beginners With Google Sheets & App Script

API Tutorial For Beginners With Google Sheets & App Script

coodingdessign.com

Subscribe to my newsletter and never miss my upcoming articles

In this API instructional exercise for beginners, you’ll figure out how to interface with APIs utilizing Google Apps Script, to recover information from an outsider and show it in your Google Sheet.

IT will tells you the best way to utilize Google Apps Script to interface with a basic API to recover some information and show it in Google Sheets:

Prerequisites

The goal here is to take an in-depth look at deploying API’s over google spreadsheet using script editor. To find script editor , go to tools > script editor

image.png

You will find a script editor console here, which will have a predefined function. On the right side, you will have the option of a file in which you can insert as many files as you want. In addition to JavaScript, if you want to do a custom HTML, you will get the option.

Now let’s see by assigning Api inside the function,

image.png

var url = "YOUR_API_URI" 
var response = UrlFetchApp.fetch(url) 
var data = response.getContentText()

Here I have created a custom function, you can use below methods for HTTP request, like Ajax or HTTP request of Javascript. Once you send request you will get the response which you can parse same as like javascript methods using

var result = JSON.parse(data);

This is where you need to console. You can use this function that will show you the response

Logger.log(var_name);

Logger.log() is same as like console.log(), But here it won’t depicts respond in your browser.

It will return result in a window which will be below at your editor named as Execution Log.

image.png

After parsing, you now have to link the spreadsheet in which to print the result. For which use below method

var ss = SpreadsheetApp.getActiveSheet();

Create a header if you want to assign a header, And append to spreadsheet

var headerRow = ["Title", "Impressions", "Clicks", "Revenue","Date","Time"]
ss.appendRow(headerRow)

Now run the loop by printing the results you want.

for(var i=0;i<result.data.length;i++){   

     var row = [result.data[i].tag_id,
                result.data[i].impressions,
                result.data[i].clicks,
                result.data[i].money,
                result.data[i].date,
                currentTime]
              //Logger.log(row);
                  ss.appendRow(row);

   }

After the loop completes open your active sheet and you will get all the results from the API.

image.png

I hope you will find this article helpful. if you face a problem or want to make it easy, you can contact us .

Happy Coding

DON'T MISS

1.How to Debug a Node.js application in a Docker Container

2.How to access wordpress functions and database in custom php file

3.How to speed up your TypeScript project? SWC – super fast JS/TS compiler

#programming-blogs#javascript#web-development#100daysofcode#codenewbies
 
Share this