Jan 2018
21

google sheets

how to update employee details in google sheet

What was the original method that was chosen for entering new employees and employee changes

Our original approach was simply to supply a sheet with the basic information for each employee (name, address, SSN, etc.) and another sheet where the client could enter employee additions and changes. We planned on the client keeping the employee information sheet current and also entering data for new and existing personnel on the “Employee Updates” sheet. Primarily we just wanted to provide an organized method for submitting employee updates and, in addition, we wanted to avoid having to replace any of the personnel information after each payroll ourselves. We wanted to avoid additional work since we were already processing the payroll, building the encrypted PDF report file, making the federal and state tax deposits and handling any 401 (K) and/or direct deposit files.

 

What did the Employee Updates sheet look like

It was designed so that the client would enter one or more rows of changes for an employee by selecting the employee name from a drop-down list, selecting the type of update (address, federal tax status, voluntary deduction, etc.) from another drop-down list, and then supplying any required information in some additional columns. For example, to enter direct deposit information for John Jones the client would select John Jones from the name drop-down box, select “Direct deposit account” from a second drop-down box and then enter the employee’s bank routing number, type of account (checking or savings), and bank account number in three columns labeled V1, V2 and V3.

 

How did you create the drop-down boxes for the employee names

The employee name drop-down was created by using Google Sheets’ Data Validation feature. We added data validation to the “Name” column of the Employee Updates sheet as a drop-down box and linked it to the employee names in the “Name” column of the “Employee Listing” sheet:

The drop-down box in the “update items” column was also created using data validation, but in that case the choices in the drop-down list were taken from a list in column A on the “Employee Update Item List” sheet.

 

Did you run into any particular problems with this design

The main thing we were concerned with (at this point) was to keep the total width of all the active columns as compact as possible to that all the data could be printed – in landscape mode – on an 8 ½ x 11 inch piece of paper (or at worst, on a sheet of legal size paper). We wanted to be able to do that so that we could get a hard copy without having to jump through hoops.

 

Here’s what the Employee Updates sheet looked like:

 

How did that design work out

There were two major problems with the original design:

 

1 ) Our clients were used to having the employee data available separated on different tabs such as Personal Data, Job Data, Tax Data, Direct Deposit Data, Pay Rates and Leave Data rather than on Employee Listing sheet ( which only included part of that information).

 

2 ) If the client needed to make a large number of employee additions and/or changes the updates had to be entered on the Employee Updates sheet and also on the Employee Listing sheet.

 

OK, what next

Something one of our clients said gave us an idea. She asked if we couldn’t provide a tab or sheet for each type of employee information the way we had done on the PC system. The client would add or change the employee information right there on the appropriate sheet and we would pull the updates directly from those sheets. After some discussion we decided to keep the Employee Updates sheet for clients who didn’t really care about having all the different employee data on the spreadsheet, but we would set up a separate sheet for each type of employee information (personal data, job data, pay rates, etc.) for the other clients. Determining what fields (cells) had been added or changed on the different sheets was a problem though. On the PC system we actually compared the current data with a back-up copy from the previous payroll in order to find the current period changes, but there was no easy way to do that in Google Sheets.

 

So how did you go about finding the employee updates on EZ Pay Sheets

After doing a little reading about spreadsheets we realized that we could locate the changes visually if we could automatically change the background of the cells (fields) that had been added or modified. In order to do that we had to explore Google Apps Script or GAS (which is basically a version of JavaScript). GAS allows you to add custom programming to Google Sheets for situations where standard spreadsheet functions and formulas won’t work for what you need to do.

 

So how did you manage to change the cell background when an update was made

We used a script that automatically changed the background color of any cell that was modified from the normal white to yellow – then we would manually change all cell backgrounds back to white once the payroll was processed (see the next section for details on the script). The PayRates sheet with some updated rates looks like this:

 

What if an item was changed by mistake? How does the client get rid of the yellow background

Actually it doesn’t really matter – at worst we would go to the employee’s record to make the change and find out that “new” value for the particular item was the same as the existing value. If the client wanted to change the background back to white it’s easy to do – with the “modified” cell selected select the “Format” menu, then select “Clear Formatting”.

 

What if the client accidentally does something that clears all the formatting – including the background highlighting of the cells that have been edited

Then you can click the backward curving arrow on the menu bar (or press CTRL-Z) to “undo” that action. If for some reason that doesn’t work you can make use of Google Sheets “revision history”. Google Sheets stores a backup of your spreadsheet every time a cell is edited and the user leaves the cell. You can select the “File” menu, click on “see revision history”, click on a time-stamped copy of your spreadsheet that was made prior to the action that wiped out the background highlighting and select to restore that copy:

 

Once you click on “Restore” the backup spreadsheet will be copied over the current version.

 

 

If You Want To Respond,Please Send Me An Mail

My Email ID : mothi@mydigitalline.com

Facebook Feed

Unable to display Facebook posts.
Show error

Error: Error validating application. Application has been deleted.
Type: OAuthException
Code: 190
Please refer to our Error Message Reference.

Newsletter