This is how you clock-in in Google Sheets (Version 2)

If you haven’t tried the previous one, here’s the post. You actually need to read that post because all instructions regarding creating the Google Sheet is still the same (with some minor changes, will discuss later) and I won’t repeat it here. The thing that’s changed is how we create the Apps Scripts.

For context, in the previous version, you have to enter your Employee ID before you clock in. This is to identify the employee who actually clocked in. The problem with this is that, we won’t actually know if the entered employee ID was correct for the actual person who entered it.

That’s why there’s a key, provided by Google, that we can use to compare to the Employee ID entered. Not a perfect solution, but it works. This key is tied to the employee’s gmail account. Thus, if he/she uses his/her email to enter a different employee ID, it would then show that this specific key (or email, you could say) is clocking in with different employee ID. But again, as mentioned, this is not perfect as Google changes this key every 30 days (also mentioned in previous post).

So, in this post, we will improve our Apps Script to solve the above problem and the best thing is, the employee doesn’t need to enter his/her Employee ID anymore.

Before proceeding, I have to mention that the problem above, is not a problem, AT ALL, if your organization is using Google Workspace. Thus, the following setup is not designed if you’re a workspace user.

Let’s get started (don’t forget to create the Google Sheet based on this previous post. Do the Google Sheet part only. Skip the rest). Go on, I’ll wait…

Just to add an emphasis, don’t forget to name the sheet timesheet. Not the spreadsheet but the sheet name.

Regarding the changes that you need to do in your Google Sheet, change the key column header to email and remove the employee_id column. Here’s how it would look now:

In your Google Drive, create an Apps Script.

You might want to create a folder and put the apps script inside as we will create two Apps Scripts in this post. Plus, you might want to move your created Google Sheet here too, just for organization but it’s your choice.

You might get this pop-up when you create your apps script. Read it then click Create script.

You will see a screen like this. This is a Code.gs file where we can put Apps Script codes.

Copy the code from this Code.gs file and replace everything in your Code.gs.

Your Code.gs file will now look something like this.

In this code, you have to update the TIMESHEET_SHEET_ID as pointed by the arrow. Go to your google sheet. The sheet ID is the long random string in the URL.

After you update the code, it would look like this:

Click save.

On the upper right, click Deploy > New deployment.

On the pop-up window, click the gear icon, then select Web app.

Enter any Description you like, keep Execute as as Me, change Who has access to Anyone, then click Deploy.

Take note that even if this apps script’s access is set to Anyone, this script would not, and should not, be shared to anyone. Solely you, the creator of this timesheet, should have access to this script.

Wait for the deployment to finish, then click Authorize access.

Login to your Google account. Take note that the title of your Apps Script project will show here.

Click Advanced.

Click Go to <project-name> (unsafe).

Take note that the above is fine even if it says not verified or unsafe. It’s your apps script, so it’s fine.

Read the permissions that you will allow (might not be the same as the screenshot) before proceeding. Then click Allow.

If you encounter any error, just try to deploy again.

When successful, you’d get your URL. Copy the URL for later use.

If you closed the New deployment window without having the chance to copy the URL, don’t worry. Just click Deploy again and click Manage deployments. Here is where you can get the URL.

Okay. One down. We need one more script. By now, you know how to create one. Go ahead and create one.

Done? Okay. Copy this code to your Code.gs file. This time, you have to update this part of the code and replace it with the URL of the other script that we deployed earlier. Remember? We copied it for late use? This is the later use.

When replaced, it would look like this:

Save the file then create an index.html file.

Name it index.

You now have a new index.html file.

Now, copy the code here and paste it in the index.html file you’ve created. Take note to replace the existing code.

Save the file then deploy. You know how to deploy right? But this time, deploy with these settings:

• Description: Still anything you like.
• Execute as: User accessing the web app
• Who has access: Anyone with Google account

After deployment, you will have another Apps Script URL for this new script. Take note. This new URL is what you will give to employees for them to use, not the other one.

Open the URL. When prompted, click Review Permissions. Then proceed with allowing the permissions just like before.

If all is well, employees can now clock in just by logging in to their Google Account, opening the URL, and clicking the In button.