Case Study 1

Bob work in a company which uses spreadsheet to collect responses from users through a form. Bob gets thousands of submission daily, over a weeks time, the spreadsheet becomes huge causing the performance issues while viewing or editing, So he need to copy the whole data from submission sheet to a  new spreadsheet and clear the original sheet evreyday or everyweek.

Solution:

This work can be automated by writing an script which runs daily or weekly (by adding it to triggers).

This script Copies the whole data from Submission sheet, creates a new Spreadsheet and put all the data in newly created sheet.

After that, it clears the original sheet.

Here is the example script.

function executeIt(){

  //get the date from current Spreadsheet

  var ss = SpreadsheetApp.getActiveSheet();

  var dataRange = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn());

  var myData = dataRange.getValues();

 //Create new Spreadsheet & paste the data

var newSS =  SpreadsheetApp.create('Myfile '+getDate());

  newSS.getActiveSheet().getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).setValues(myData);

  //Clear the original sheet except header row

  ss.deleteRows(2, ss.getLastRow()-1);

}

//Function to get Date & Time

function getDate(){

var d = new Date();

  var dateofDay = new Date(d.getTime());

  return Utilities.formatDate(dateofDay, "GMT+2", "MM-dd-yyyy hh:mmm:ss z");

}

function 'executeIt()' is added to trigger to run daily or weekly.