Google App script is a scripting platform developed by Google for lightweight application development. Like in your company you have work very human-based and it is done on a daily basis. You can create an application to automate those things. You can write a script to automate those things.
The entire Google script is written in JavaScript. And if your data volume is low then Google app script is a perfect mode to write the application. Google App script is free. whatever you write on the Google app script is hosted on Google’s own server. All the server parts and DevOps part is handled by Google itself.
Limitations: Google App scripts have limitations, such as Free users getting 90 minutes of daily processing time.
How to open Apps Scripts Editor
We will use Google Sheets for writing scripts. Once you are in your Google sheet. Go to Extensions Click on Apps Scrip, it takes you to the page called Apps Script Editor. Now this is the Page where you are going to write all your functions and all your code and then run the function or create a special menu that shows on the Google Sheet Nav bar.
Brief About Software Codes – Very Simple examples
function my_demo()
{
var a = 5;
var b = 10;
var c = a+b;
Logger.log(c);
}Here we are writing a function that will print a table of 3 up to 5 terms.
function print_table(){
var row = 5;
var number = 3;
for(var a = 1; a <= row; a++){
var value = a*number;
print(value);
}
}Read data from the Google Sheet
function read_from_sheet()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet1');
var val1 = sheet.getRange('A1').getValue();
var val2 = sheet.getRange('A2').getValue();
Logger.log(val1);
Logger.log(val1 + val2);
Logger.log(val2);
}Write data to Google Sheets.
function read_write_to_sheets()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName('Sheet1');
var sheet2 = ss.getSheetByName('Output');
var s2 = ss.getSheetByName('Sheet2');
var source_color = s2.getRange('B1').getBackground();
var source_font_color = s2.getRange('C1').getBackground()
var n1 = sheet1.getRange(5,6).getValue();
var n2 = sheet1.getRange(10,4).getValue();
var sum = n1+n2;
var r= sheet2.getRange('D3');
r.setValue(sum)
r.setBackground(source_color)
r.setFontColor(source_font_color);
s2.getRange('E3').clearContent().clearFormat();
}How to create a new Spreadsheet
function create_new_ss()
{
var name_of_ss = 'By Script';
var new_ss = SpreadsheetApp.create(name_of_ss);
var new_ss_url = new_ss.getUrl();
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('New Sheet');
sheet.getRange('A2').setValue(name_of_ss);
sheet.getRange('B2').setValue(new_ss_url);
}How to delete a sheet
function delete_sheet()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet6');
ss.deleteSheet(sheet)
}How to Rename the sheet
function rename_sheet()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet7');
sheet.setName('Renamed by Script');
}Reading and writing multiple cells in the Google Sheets
function demo_write_multiple_cell()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var input_sheet = ss.getSheetByName('2D');
var Output_sheet = ss.getSheetByName('Output');
var last_row = Output_sheet.getLastRow();
var next_row = last_row+1;
Logger.log(next_row);
var data = input_sheet.getDataRange().getValues();
Logger.log('total row ='+data.length);
Logger.log('total coloumn ='+data[0].length);
Output_sheet.getRange(next_row,1,data.length, data[0].length).setValues(data);
}Some examples with IF Statement/ For Loop/ While Loop
function demo_if()
{
var age=20;
if(age>=18)
{
Logger.log('Can Vote');
}
Logger.log('Program is over');
}
function demo_if_else()
{
var age=10;
if(age>=18)
{
Logger.log('Can Vote');
}else
{
Logger.log('can not vote')
}
Logger.log('Program is over');
}
function demo_multiple_if_else()
{
var age='shahbaz';
if(age<10)
{
Logger.log('you are a kid')
} else if(age<20)
{
Logger.log('you are a teen')
} else if(age<55)
{
Logger.log('you are young')
} else
{
Logger.log('You are senior citizen')
}
}
function check_voting_age()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('IF');
var age = sheet.getRange('B1').getValue();
var result = '';
if(age>=18)
{
result = 'Yes'
}else
{
result = 'No'
}
sheet.getRange('B2').setValue(result);
}Basics Projects
Project 1: Combine data from an Individual sheet to a Master sheet
There is a company that has a number of sales members and each of them enters their daily summary in one tab. And we want to merge all those summaries in one master tab.
In this project, we fetch the data from each member tab and append it to the Master tab in the Google sheet.
function merge_tabs() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var setting_tab = ss.getSheetByName('Setting');
var sales_team = setting_tab.getDataRange().getValues();
var master_sheet = ss.getSheetByName('Master');
var date = new Date();
for(var a=1; a<sales_team.length; a++)
{
var this_sales_person = sales_team[a][0];
var sheet = ss.getSheetByName(this_sales_person);
var data = sheet.getRange('A2:C2').getValues()[0];
data.unshift(this_sales_person);
data.unshift(date);
/*
var to_print = [data];
Logger.log(to_print);
var next_in_master = master_sheet.getLastRow()+1;
master_sheet.getRange(next_in_master,1,to_print.length, to_print[0].length).setValues(to_print);
*/
master_sheet.appendRow(data);
}
}Intract with Google Drive and UI Elements using Google Apps Scripts.
Get Files and Folders from Drive.
Fetched all the file name, file URL, file ID, owner, file size. Created the function get_all_files, In the function first we get the sheet by name and then we hit the drive, we need to use the Drive API under the services. Go to the services and click on the Drive API and then click on the add. After adding the drive use the DriveApp getFiles function. getFiles function Gets a collections of all files in the User’s Drive.
function get_all_files() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Files');
var files = DriveApp.getFiles();
var final=[];
while(files.hasNext())
{
var one_file = files.next();
var name = one_file.getName();
var url = one_file.getUrl();
var id = one_file.getId();
var owner = one_file.getOwner();
var size = one_file.getSize();
var file_type = one_file.getMimeType();
var temp = [name, url, id, owner, size, file_type];
sheet.appendRow(temp);
}
}function get_all_files_from_root() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Files');
var root = DriveApp.getFolderById('_insert_id_');
var files = root.getFiles();
var final=[];
while(files.hasNext())
{
var one_file = files.next();
var name = one_file.getName();
var url = one_file.getUrl();
var id = one_file.getId();
var owner = one_file.getOwner();
var size = one_file.getSize();
var file_type = one_file.getMimeType();
var temp = [name, url, id, owner, size, file_type];
sheet.appendRow(temp);
}
}
function transfer_files()
{
var source_folder_id = '_insert_id_';
var target_folder_id = '_insert_id_';
var source_folder = DriveApp.getFolderById(source_folder_id);
var target_folder = DriveApp.getFolderById(target_folder_id);
var source_files = source_folder.getFiles();
while(source_files.hasNext())
{
var this_file = source_files.next();
this_file.moveTo(target_folder);
}
}
function rename_folders()
{
var source_folder_id = '_insert_id_';
var target_folder_id = '_insert_id_';
var source_folder = DriveApp.getFolderById(source_folder_id);
var target_folder = DriveApp.getFolderById(target_folder_id);
source_folder.setName('Source Folder');
target_folder.setName('Target Folder');
}
function save_as_pdf()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Files');
var sheet_id = sheet.getSheetId();
var folder_id = '_insert_id_';
var folder = DriveApp.getFolderById(folder_id);
var url = Drive.Files.get(ss.getId()).exportLinks['application/pdf'];
url = url + '&size=A4' + //paper size
'&portrait=false' + //orientation, false for landscape
'&fitw=true' + //fit to width, false for actual size
'&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
'&gridlines=false' + //false = hide gridlines
'&gid='+sheet_id+
'&fzr=false'+ //do not repeat row headers (frozen rows) on each page
'&top_margin=0.00'+ //All four margins must be set!
'&bottom_margin=0.00 '+
'&left_margin=0.00'+
'&right_margin=0.00'+
'&horizontal_alignment=CENTER'+
'&vertical_alignment=CENTER';
Logger.log(url);
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {headers: {'Authorization': 'Bearer ' + token}});
var new_pdf = folder.createFile(response.getBlob().setName('New PDF'));
var new_url = new_pdf.getUrl();
Logger.log(new_url);
}
function send_pdf_email()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Files');
var sheet_id = sheet.getSheetId();
var url = Drive.Files.get(ss.getId()).exportLinks['application/pdf'];
url = url + '&size=A4' + //paper size
'&portrait=false' + //orientation, false for landscape
'&fitw=true' + //fit to width, false for actual size
'&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
'&gridlines=false' + //false = hide gridlines
'&gid='+sheet_id+
'&fzr=false'+ //do not repeat row headers (frozen rows) on each page
'&top_margin=0.00'+ //All four margins must be set!
'&bottom_margin=0.00 '+
'&left_margin=0.00'+
'&right_margin=0.00'+
'&horizontal_alignment=CENTER'+
'&vertical_alignment=CENTER';
Logger.log(url);
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {headers: {'Authorization': 'Bearer ' + token}});
var blob= response.getBlob();
var to = '[email protected]';
var subject = 'this has attachment';
var body = 'Please find the pdf of the data';
GmailApp.sendEmail(to,subject,body,{attachments:[blob]})
}
Leave a Reply