App Script Cheatsheet

In this article, we shall see some most powerful google sheet APIs to work with in order to automate most of the google sheet workload. We shall use Appscript in order to achieve above.

So let’s start.

Following are the Topics we shall see in this whole article:

i) Copy tab of Sheet to another Sheet

ii) Rename and Delete Tab

iii) Update Cell Value

iv) Delete Column

v) Update Column Format

vi) Shift and Insert Column

vii) Add new Column With validation

viii) Mail Google Sheet as an excel file

1. Copy tab of Sheet to another Sheet

function copyWholeTabToAnotherSheet() 
{ 
   var source = SpreadsheetApp.getActiveSpreadsheet();
   var tab = source.getSheetByName('Tab');
   var dest = SpreadsheetApp.openById("Destination Sheet ID");
   tab.copyTo(dest);  //creates new tab if not present 
}

Above Snippet of code copies the ‘Tab’ tab of Current Sheet to Provided Destination Sheet whose ID will be in place of ‘Destination Sheet ID’.

If that tab is not already present in destination sheet then it will automatically create new tab.

2. Rename Tab

function renameTabName() 
{ 
   var destination_sheet = SpreadsheetApp.openById("");  
  
   // rename the tab 'Tab' to 'Tab Renamed'
   destination_sheet.getSheetByName('Tab').setName('Tab Renamed'); 
}

Above snippet Renames the tab named as ‘Tab’ of provided sheet to ‘Tab Renamed’.

3. Delete Tab

function deleteTab()
{   
  var destination_sheet = SpreadsheetApp.openById("");  
  var sheet = destination_sheet.getSheetByName('Tab');  
  destination_sheet.deleteSheet(sheet);  
}

4. Update Cell Value

function updateCellValue() 
{ 
   var destination_sheet = SpreadsheetApp.openById(""); 
   
   // get the tab where you want to make update
   var destination_tab = destination_sheet.getSheetByName('Tab');
 
  // update the cell A4 of that tab value as 'f'  
  var cell_value = destination_tab.getRange('A4').setValue('Value'); 
}

Above Snippet updates the value of A4 Cell to ‘Value’.

5. Delete Column

function deleteColumn() 
{    
   var destination_sheet = SpreadsheetApp.openById("");  
   var tab = destination_sheet.getSheetByName('Tab Renamed');            
   
   //delete 1st column  
   tab.deleteColumn(1); 
}

6. Update Column Format

function updateColumnFormat() 
{    
   var destination_sheet = SpreadsheetApp.openById("");  
   var tab = destination_sheet.getSheetByName('Tab');       
   
   //get the column range whose format you want to change  
   var column = tab.getRange('B1:B')    
   
   // Plain text  
   column.setNumberFormat("@");   
  // Simple date format  
  column.setNumberFormat("yyyy-mm-dd");   
  // Another date format  
  column.setNumberFormat("dd-mm-yyyy");
}

Try to Change to another format using google Developer Documentation.

7. Shift and Insert Column

function shiftOrInsertColumn() 
{    
   var destination_sheet = SpreadsheetApp.openById("");  
   var sheet = destination_sheet.getSheetByName('Tab');  
  
   // Shifts all columns by three  
   sheet.insertColumns(1, 3);  //(index, howmany)
   // This inserts a column in the second column position 
   sheet.insertColumnsAfter(1);  //(afterposition, howmany)   
 
   // This inserts five columns before the first column   
   sheet.insertColumnsBefore(1, 5);
}

8. Add new Column With validation

function addColumnInTabWithValidation() 
{  
  var sss = SpreadsheetApp.openById('');       
 
  // prepare range column where you want to add validation       
  var column = sss.getRange('Tab!J2:J');
  // get validation-things/rule which you want to add on that cell    
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sss.getRangeByName('Vals!U2:U')).build();      
  // add cell validation    
  column.setDataValidation(rule);
}

9. Mail Google Sheet as an excel file

function getGoogleSpreadsheetAsExcel(){
   var receipients = ['abc@gmail.com', 'def@gmail.com']
   var to = receipients.join();
  
   var url = "https://docs.google.com/spreadsheets/d/"+SpreadsheetApp.getActiveSpreadsheet().getId()+"/export"+"?format=xlsx&"
   var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
   var blob = UrlFetchApp.fetch(url, params).getBlob().setName("File.xlsx");
   
   var sender = 'sendermail@gmail.com';
   
   var subject = (" Subject Goes Here ");
    
   var body = "Hi,<BR><BR> Text Body of mail Goes Here" +  "\n";
    
   GmailApp.sendEmail(
           sender,          
           subject,          
           "Requires HTML",           
           {
             name: 'Title Of Sender Name', 
             htmlBody: body,
             cc: to, 
             attachments: [blob] 
           } 
   );
}

That’s it for now. I shall update more important functions as I go through them.

Thanks and Happy Coding.

Let me know in comments if you find any issues or connect with me on linkedin.

Github Repo link for code.

Resources:

1. https://developers.google.com/

2. https://stackoverflow.com/