online sales entering sheet for organisation having multiple branch

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/