Online Sales Entering spreadsheet using google script
As our new software bee bee is down for last few months I T head +bithesh soubhagya assign me a task to create a parallel online sales entering application which helps users to inform their sales to regional office, thus we can debug our bee bee software by informing head office the actual sales. The project was great success, the data was informed head office daily and they correct it on bee bee software.Created an online sales entering spreadsheet using google script code. The sheet changes daily so that end users can add/enter their daily sales and other details for that day, . The google script in background will automatically send reports daily as email both in html and pdf formats, The sheet keeps a backup every month in google drive, Once backup is created the sheet will clear the whole data entered in the spreadsheet.
Link to google spreadsheet
Back up
 |
| These backup are created using google script code |
Algorithm
1. Start
2. Declare variable columns, day, weekends, month, year
3. get todays date from google server (format dd/mm/yyyy)
4. get day from todaysDate
5. check if sunday then skip
6. else hide entire sheet
7. show sheet (day) // say 1,2,3...etc
8. Stop
Source code
function algorithm(){
var sheetToPdf = SpreadsheetApp.openById("url to sheet ");
var columnRanges = ["A:A","B:N","O:AA","AB:AN","AO:BA","BB:BN","BO:CA","CB:CN","CO:DA","DB:DN","DO:EA","EB:EN","EO:FA","FB:FN","FO:GA","GB:GN","GO:HA","HB:HN","HO:IA","IB:IN","IO:JA","JB:JN","JO:KA","KB:KN","KO:LA","LB:LN","LO:MA","MB:MN","MO:NA","NB:NN","NO:OA","OB:ON"];
var todayDate = new Date();
var day = todayDate.getDate();
var actualDay = day - 1;
var saleDay = day;
var colToHide = day -1;
var colToHide2 = day -2;
var colToShow = day;
var actualMonth = todayDate.getMonth() + 1;
var rangeHide = sheetToPdf.getRange(columnRanges[colToHide]);
var rangeToShow = sheetToPdf.getRange(columnRanges[colToShow]);
sheetToPdf.unhideColumn(rangeToShow);
sheetToPdf.hideColumn(rangeHide);
if(day > 3){
var rangeHide2 = sheetToPdf.getRange(columnRanges[colToHide2]);
sheetToPdf.hideColumn(rangeHide2);
Logger.log(" Previous date script running issue solved ");
}
Logger.log("Date : "+todayDate.getDate());
Logger.log("col to show : "+colToShow )
Logger.log("range to show : "+columnRanges[colToShow] )
Logger.log("Date : "+todayDate.getDate());
Logger.log("col to hide : "+colToHide )
Logger.log("range to hide : "+columnRanges[colToHide] )
Logger.log(todayDate.getDay());
Logger.log(todayDate.getDate());
Logger.log(todayDate.getMonth());
Logger.log(todayDate.getYear());
//if day is 1
if(todayDate.getDate()==1){
//Logger.log(' Today is first day ');
//rangeToShow = sheetToPdf.getRange(columnRanges[colToShow]);
sheetToPdf.unhideColumn(sheetToPdf.getRange(columnRanges[0]));
sheetToPdf.unhideColumn(sheetToPdf.getRange(columnRanges[1]));
sheetToPdf.hideColumn(sheetToPdf.getRange(columnRanges[28]));//28,29,30,31
sheetToPdf.hideColumn(sheetToPdf.getRange(columnRanges[29]));
sheetToPdf.hideColumn(sheetToPdf.getRange(columnRanges[30]));
sheetToPdf.hideColumn(sheetToPdf.getRange(columnRanges[31]));
}
// sheetToPdf.copy("SALES_ON_"+todayDate.getYear()+"_"+actualMonth+"_"+saleDay);
sheetToPdf.rename("SALES_ON_"+todayDate.getYear()+"_"+actualMonth+"_"+saleDay);
}
Email templates
Thanks for accounts manager +deepajayaprakash payyanakkal for her support in creating this wonderful application for regional office kozhikode...
Thanks to +Consumerfed IT Division for assigning this work.
http://javabelazy.blogspot.in/
