Articles, Blog

Digital Time Card using Forms and Sheets | The G Suite Show

October 30, 2019

welcome to another episode of “The G Suite Show.” Today we have a new
business process, courtesy of our Transformation Gallery. And we’re so fortunate to be
joined by our lovely producer, Heather. HEATHER CROSS: Hola. Bonjour. Konnichiwa. Tudo bem? KEVIN HANSON: Heather,
should I refer to you as Carmen Sandiego? HEATHER CROSS: I wish. No, you just reminded me that
the Transformation Gallery now comes in five languages. So we have English, of
course, French, Portuguese, Spanish, as well as Japanese. KEVIN HANSON: Oh, awesome. Great work, Transformation
Gallery team. We work with a lot of
different contractors on site, and we’ve come up with a system
to digitally record timesheets. HEATHER CROSS: So
today we’re going to be using Forms and Sheets
to record employees’ time entry data. In Forms, it will be like
a modern-day time card. KEVIN HANSON: And the
results of the form get sent to a
Google Sheet, where we can add formulas to compute
things like daily hours worked. Let’s jump in and take a look. First, we’ll go to Drive,
and create a new form. Enter in the
corresponding fields you want your employees
or contractors to fill out, like
Department and Manager. You can set up the form
to automatically collect the contractor’s email ID, so
we know who filled out the form. HEATHER CROSS: There is a Select
Question Type in Forms where you can add a field for time. That way, all the
times collected are in the same format. That makes it easier to
run formulas and analyze the data later. I’ve also created a separate
Special Notes section, if a contractor needs to
enter a scheduling change, say, overtime or holiday hours. KEVIN HANSON: All of this gets
tracked in the Responses tab, and the managers are notified. I can see the responses
right here in Forms, or I can send the
data to a spreadsheet. I can even change the
destination sheet at any time, like if I want to keep a
new sheet for each month. HEATHER CROSS: And the
cumulative responses will only be visible to the manager. However, there is an
option to send the employee a copy of their response after
they’ve submitted the form. This option is in the Settings
Gear of the form, Collect Email Address, Response Receipts. You can choose either
If Requested or Always. KEVIN HANSON: And
there’s no more grabbing individual
pieces of paper or trying to organize
random Excel files and bringing them all together. All the data is automatically
collected into a single sheet. And we never lose
the data, which is important if any auditors
come walking around. HEATHER CROSS: And
you may be wondering how your contractors
can access this form, especially if they’re a
field worker without a desk. KEVIN HANSON: It’s easy. You can access the
form via mobile device, or they can add a shortcut to
the form on their laptop home screen. I can email them a
link, or embed the form in a Google site. HEATHER CROSS: If your
employees or contractors are in a factory, it’s
easy to set up a Chromebook kiosk with the form
for the contractors to login and enter their times. KEVIN HANSON: There are quite a
few ways you can implement this into your business or team. HEATHER CROSS: And if you find
another way of using this use case, make sure
to submit feedback to the Transformation Gallery. They love hearing from you. KEVIN HANSON: That’s right. And I actually
think this would be an excellent process for my
weekend birdwatching club. We’re logging all of
hours watching birds, and we’re having a little
bit of a competition. HEATHER CROSS: Bird watching? KEVIN HANSON: Yeah. And by the way, we are
accepting new members. I’d encourage you to apply,
but we are highly selective. HEATHER CROSS: I’m going to
hard pass on that one, Kevin. But thank you. KEVIN HANSON: It’s your loss. It’s your loss. Well, now that we’ve set up the
form, let’s head to the sheet itself and take a look. In the form, go to Responses. Click the Spreadsheet icon in
the upper right-hand corner of the form. The first time
you click on this, it will ask you if you
want to create a new sheet. Click Yes. HEATHER CROSS: In
the sheet, you will see a Time Stamp,
Date, Employee’s Name, and corresponding
fields you’ve created. In this example, we can
see the additional fields that we’ve used, such as
Department and Special Notes. KEVIN HANSON: You can
also sort by departments, which would make
it easier if you’re a vendor manager
using contractors for different departments. HEATHER CROSS: We’ve also
added our own columns to the sheet for managers
to add approvals, and to calculate the total
time worked that day. The formula is simple. Just subtract time
out of time n, and then use the number
format for duration. KEVIN HANSON: As
the manager, I now can sort through
our time card data to better budget hours on
specific projects or forecasts for next year’s busy season. HEATHER CROSS: And
it’s important to note that employees do not
have access to the sheet, so they can’t see each
other’s submission. KEVIN HANSON: And if using
this just for a single team, a good tip is to use this
as a project time tracker. For example, if you
have one set of the team working on something for
the first half of the day and a different set of the
team working on something for the second half of the day. HEATHER CROSS: It would be
easy for you, as their manager, to allocate time and
budget towards a project. KEVIN HANSON: Well,
we hope you take a look at our
Transformation Gallery, now in five different languages. You can check it out here. HEATHER CROSS:
See you next week. Ciao! KEVIN HANSON: Bye. SPEAKER 1: Check out
last week’s episode for tips on how
to use just a few of G Suite’s many
accessibility features.


  • Reply Frederick Dante Guerra June 20, 2017 at 7:03 pm

    This is exactly what I've been looking for!!

  • Reply Adrian Leonard Canning June 20, 2017 at 8:14 pm

    Hi there has been no update video of what's new in docs, sheets and slides.

  • Reply Hugo Duarte Capela Novais Teixeira June 20, 2017 at 8:42 pm

    Portuguese of Brazil??!! REALLY??! What about the real Portuguese??? Portuguese of Portugal

  • Reply Weilies Chok June 20, 2017 at 11:17 pm

    how would it deal withโ€ฆ
    * Worker submit attendance on behalf of another workers?
    * Worker submit remotely(at home) where they not even in factory
    * submit as HR dept but they actually from Finance department
    * Track the LUNCH IN and OUT

    the above problems cam be resolve if the company hire a supervisor/administrator and ensure things work as expected (extra cost+no automation)

    I think google trying to showcase in best scenarios, where things work perfectly. We evaluated many solutions end up we have to get a fully dedicated time tracking system with finger print scanning and fully linked up with their sys wrote in aspx , so there is totally no google tech involved

    personally I like google but they have to try hard when we talk about enterprise solutions.

  • Reply Malcom Clark June 21, 2017 at 4:35 am

    hey I really want to know but to the people watching this , do you have a current usecase for this technique?

  • Reply Kambaa June 21, 2017 at 5:43 pm

    I need my office wifi to identify my google form via my mobile and capture the entry and out every time my wifi connects and disconnects – is it possible

  • Reply ordxpres June 22, 2017 at 1:00 pm

    I'd love to see a different layout options. The current flow is bad. I'd like to be able to create a form that looks good on a pc screen (16*9 ratio) and then turns into a current flow if opened up on a mobile device)

  • Reply andrew storm June 22, 2017 at 9:38 pm

    Hi Gsuite team! I want to migrate our tennis club from a free gmail account to a paid Gsuite account. Is there any discounts for non profit organisations like a sports club?


  • Reply Step Up For Mental Health June 26, 2017 at 3:10 am

    Great will try this, as soon as I can figure out the Transformation gallery. ๐Ÿ™‚

  • Reply M June 27, 2017 at 10:08 pm

    Great job, guys!

    I've been implementing many management tools with google docs even when they didnt exist like Google Fusion Table linked to Google Maps and now this Digital Time Card.

    There's a LOT of basic things to implemment in Google Forms! A LOT!
    Why, until these days, G Suite Team doesnt implement in Google Forms or Google Sites a simple box that allows users to upload basic files, with limited sizes (like .JPEG .PDF), from a PC or even from a mobile camera, to a SHARED Google drive folder?

    Basic things missing in Google Forms:
    *Files Upload Options
    *Captcha to prevent spam
    *No E-Signature but it could be solved with File upload options (in a certain way)

    More people uploading files to Google servers by the google forms would demand manager's users to Upgrade their accounts and give Google more money!

    Thank you
    Push like if you agree
    Sorry about english

  • Reply Joaquin Rdz July 11, 2017 at 2:00 am

    Google forms Offline

  • Reply Juan Ricardo Flores Pizarro July 18, 2017 at 8:27 pm

    Want to ask how can we setup especific hrs for particulary activities?

  • Reply Paulo Seixas July 27, 2017 at 3:55 pm

    The Google spreadsheets are great, however, the lack of zoom, in my view, is a defect that needs to be repaired urgently by the developers. The absence of this function makes the application uncomfortable and unattractive to Excel users. I also noticed in browsing the help forums that this is an old demand, but with no solution. If you want to get more users to your platform, start thinking about deploying this improvement in your spreadsheet application. Hugs!

  • Reply Kevin Amy September 2, 2017 at 2:21 am


  • Reply worldtvlinks November 8, 2017 at 4:10 pm

    please help me i m little bit confuse abount gsuite if i buy enterpise plan for 1 year how many can i create user and also tell me i see on price list USD $10
    per user per month..

  • Reply Mohamed Handwi February 2, 2018 at 7:59 pm

    Mohammeb Elhanbaoui555g

  • Reply Moses Kamayagl February 13, 2018 at 6:36 pm

    The App Show is an awesome show. Question: How can I use Forms and Spreadsheets to track recruitment and auto acknowledge receipt of resumes?

  • Reply Meg Jones February 28, 2018 at 2:55 pm

    I love having a time stamp for our employee to clock in… is there a way to have google sheets do conditional formatting when they clock in after 9:05:00 AM? but before 12:00:00 pm (cause of half days) I thought it would be simple "greater thans" or even "timevalue" formulats but they are not working and after scouring the internet I cant get it to work!!

  • Reply Antonio Tate April 7, 2018 at 11:34 am

    Hello, I have created the time sheet successfully, adding on the amount an employee makes, calculating their daily total. How do I add and calculate a column/question for employee break(s) in sheets and forms, respectively, along with sending the employee their time for the week from sheets?

  • Reply Bruce Sedlacek April 27, 2018 at 9:01 am

    ๐Ÿˆน๐‡๐ž๐ฅ๐ฅ๐จ ๐†๐ฎ๐ฒ๐ฌ๐ฌ, {๐†๐ซ๐ž๐š๐ญ ๐’๐ž๐ซ๐ฏ๐ข๐œ๐ž}
    ๐๐จ๐ฅ๐Ÿ• ๐†๐ข๐ฏ๐ž๐ฌ ๐๐ž๐ฌ๐ญ ๐’๐ž๐ซ๐ฏ๐ข๐œ๐ž ๐Ž๐Ÿ ๐† ๐ฌ๐ฎ๐ข๐ญ๐ž.
    ๐๐จ๐ฐ ๐ฒ๐จ๐ฎ ๐œ๐š๐ง ๐ฉ๐ข๐ง๐  ๐ฆ๐ž ๐จ๐ง ๐ฌ๐ค๐ฒ๐ฉ๐ž๐ข๐ : ๐‡๐„๐Œ๐€๐๐“_๐Ÿ๐Ÿ‘๐Ÿ” , ๐€๐ง๐ ๐–๐ก๐ญ๐ฌ๐š๐ฉ๐ฉ : ๐Ÿ—๐Ÿ ๐Ÿ•๐Ÿ–๐Ÿ‘๐Ÿ–๐Ÿ–๐Ÿ–๐Ÿ– ๐ŸŽ๐Ÿ–๐ŸŽ , #GSuiteGoogleEmailHosting

  • Reply Annu Singh May 15, 2018 at 10:49 am

    ใ€๏ฝก_๏ฝกใ€‘ cะฝฮตฮฑฯ sฮตัvฮนcฮตs ะฝฮตัฮต #GSuiteGoogleEmailHosting ใ€๏ฝก_๏ฝกใ€‘

  • Reply Wendy Lechner June 28, 2018 at 12:13 am

    This sounds good, but I've never used Google sheets and I can't figure out where to start. Guidance?

  • Reply Ashley Smith September 17, 2018 at 11:58 am

    This is not great as employees can falsify the time they actually got into work. E.g. enters 9am even though they actually got there at lunchtime. The time entry on the sheet needs to be automatically entered into the sheet when the user presses submit to avoid any employees attempting to not follow rules.

  • Reply Norm Harding October 30, 2018 at 6:24 pm

    I love the potential for me to track time with clients, but the blank form I have does not exactly show all that is in the 2017 demo here. The Settings for the form I could only see above, found it after not finding the settings accessed in vid. Not a big issue, but what is a little more confusing, as one new to Forms, is how the section for time in and time out came into the form. It was a great presentation, I just need more step-by-step info being new to this app. ๐Ÿ™‚

  • Reply Farcella Marcellus October 31, 2018 at 4:39 am

    Hi I have a question, I have tried doing this but due to high unethical signing in, co-workers assisting other co-workers to clock in or clocking in while "on the way" to work, essentially hiding the the fact that they are late to work. Is there a way for track their location?

  • Reply Misto Babaoglu March 30, 2019 at 8:13 pm

    Scenario: I have created a G Form on my G Suite account. I want the form to be deactivated on a specific date automatically. I know I can manually close it. But I want the automatic function. How do I do that?

  • Reply All Is Pretty April 9, 2019 at 9:40 pm

    How can you as the manager retire employees from the roster without going into the excel/Sheets?

  • Reply Caite Lynch May 2, 2019 at 7:53 pm

    This is great but suggested additions: how to factor in a one hour lunch? how to use timestamp to calculate hours? how to design it so employees enter time in in the during and time out in the afternoon (instead of logging the day's hours all at once). how to feed the total hours from each day into a master attendance document (i'm using this for classroom attendance for a full school year)

  • Reply mctorny May 3, 2019 at 11:25 am

    Filling out the log in is ok but how to capture the log out?

  • Reply Brooks Nunez June 18, 2019 at 5:51 pm

    Is there a way without making another form that I can have the agents submit their clock out time at the end of the day?

  • Leave a Reply