Case Studies‎ > ‎

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.