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