Automate Excel spreadsheet using App Script

I have a topic for you called App script . It is very wonderful JavaScript based technology made by Google and you can integrate with and automate tasks across Google products like Gmail Excel sheet, etc..

After learn this Topic you can easy make your Excel sheet fully Automated , Even you can configure a trigger based on specific times.Both technical and non-technical guys should find it very simple, which is my goal. sharing the code with you that will make it run at your end and give you the desired result. If you are familiar with the fundamentals of javascript and basic oops, you are a great .Hope you aware of google excel sheet and its row ,column , tabs and pivot table etc .

While you doing Automate your Excel sheet you have to decide the trigger times, API request, response and Excel Sheet 's features. I have no doubt that it simplifies your task and cuts down on manual work.

1. First open your google excel sheet .
2. Create new Tab and rename StudentData and you can change name as per your wishes.
3. Go to the Extensions and click the App Script in menu of excel sheet.
4. Now you are in App script Dashboard where you can see the Files, default code.gs ,Libraries and services in left of the menu bar on the other hand Run command top of the menu and dropdown has contained the listed function. Where OnOpen is a default function or you can make its own function as well.

The onOpen trigger runs automatically when a user opens a spreadsheet, document, presentation, or form that they have permission to edit.

The fundamental knowledge and its requirements are listed above.

Let's examine the foundational code for API integration.

SpreadsheetApp.getActiveSpreadsheet() : Returns the currently active spreadsheet, or null if there is none.

Ex. 

1.

var activeSheet = SpreadsheetApp.getActiveSpreadsheet()

activeSheet.addMenu("Knowledge Hunt", menuItems);

Now you can able to see the "Knowledge Hunt"  in top of the Excel sheet menuBar 

2.array.push(element1, element2, …, elementN) 

push method returns the new length of the array after elements are added to it.

menuItems.push({name: "Get bits Student", functionName: "GetReport"});

where menuItems is an array. "Get bits Student" will show in the dropdown menu of the top of the Sheet and GetReport is your function where you will be integrated with the API.

3.

activeSheet.getSheetByName('StudentData');

where StudentData will be Tab Name .

Once your menu has been created, you can now run the code with just a click.

I'm sharing the code for an API that outputs comma-separated data so that you can replace your own API URL and import the information column by column into an excel spreadsheet.

// Add a menu button on opening of this sheet that calls the worker function.

function onOpen() {

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet()

  var menuItems = [];  

   // menu and sub menu

   menuItems.push({name: "Get bits Student", functionName: "GetReport"});

   activeSheet.addMenu("API", menuItems);

}

// GetReport function that fetch the current 's data.

function GetReport() {

// Common variables.

const activeSheet = SpreadsheetApp.getActiveSpreadsheet()

const prDebugMsgs = true

const bitsSData = activeSheet.getSheetByName('StudentData'); // declare the tab name as StudentData

var url = "put your api url"; // put api url which retuen csv or comma-separated

var header = {

     'cache-control': 'no-cache',

     'Accept': 'text/csv; charset=UTF-8'  

};

var options = {

     "method" : "GET",

     "header" : header

};

var results = UrlFetchApp.fetch(url,options);

bitsSData.clear({contentsOnly: true});

console.log(results.getResponseCode());

//parsing the comma-separated text

bitsSData.clearContents().clearFormats();

var arrJson=csvJSON(results);

var stringify = JSON.parse(arrJson);

bitsSData.appendRow(['Date','Name']); // Date and Name sample columns name

Object.keys(stringify).forEach(key => {  

bitsSData.appendRow([stringify[key]['Sample1'],stringify[key]['Sample2']]); // Put your Json key and value accordingly 

});

}

// Parsing the CSV data into json format

function csvJSON(csv){

  var lines=csv.toString().split('\n');

  var result = [];

  var headers=lines[0].split(",");

  for(var i=1;i<lines.length;i++){

  var obj = {};   

  var currentline=lines[i].split(",");

  for(var j=0;j<headers.length;j++){

  obj[headers[j]] = currentline[j];

  }

  result.push(obj);

  }

  //return result; //JavaScript object

  return JSON.stringify(result); //JSON

}