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.


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.