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.

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.
 
 

Comments