Introduction

Passowrd managers have beome an inevitable part of our online lives. The only way not to reuse a pasword is to ue a password manager. I am a big fan of LastPass. Have been using for the past couple of years and it has been great.

While working with a team, there might be accounts that are shared, and what people typically do is to have a pattern to genereate passwords for these accounts. For e.g., [email protected] might have a password like #SecretCONTACTPassw0rd123$%^ and [email protected] might have #SecretSUPPORTPassw0rd123$%^. This is a super insecure way for setting passwords and most times people do this because they don’t want to get in to the mess of sharing passwords over a password manager.

Sharing over LastPass needs the user to know the other person’s email address and the receiving person need to have a LastPass account and have it installed. While I am a strong advocate of using password managers, I understand that everyone in a team might not be comfortable in doing so. Sure, they need to be educated and should be made aware of the threats of not using a manager. But that is not the only difficulty, the other person might be using a different password manager. Now, you’ll have to resort to sharing plain-text password over insecure channels.

Google Sheets

Enter Google Sheets. Like all the other Google Suite of applications, Sheets can be shared across a team. If your organisation is a GSuite customer, the visibility can be set accordingly such that everyone in the organisation can view and even edit the sheet. Sheets looks like an ideal candidate to share passwords. And believe me when I say this, there are teams that share passwords on Google Sheets as plain text.

How can we not do this? We need to be able to share passwords over Google Sheets, but not as plain text.

Encrypt the password with a shared secret.

If we can use a shared secret to encrypt the password and save the encrypted text in the sheet, anyone who is looking for that password can just decrypt it using the shared secret. But everyone need to have the encrypt-decrypt tools installed. This takes us back to the original problem - everyone need to have the password manager installed.

What if we could do this in Google Sheets itself?

Google Apps Script

Google released Apps Script in 2009 as scripting-language for light-weight application development platform which can integrate most of Google applications together. It is based on JavaScript and runs on the Google Cloud. We’ll use Google Apps Script to build our encryption-decryption tool into Google Sheets.

Let’s get started

Step 1 - Create a Google Sheet

This might be the easiest of all. Just create a new Google Sheet. Head to sheets.google.com and start a new blank speadsheet.

Step 2 - Add header row

Create the header row for the columns. The headings should be:

• Name
• URL
• Username
• Password

These columns in each row will be managed by our script. You can also make the header row bold and freeze it.

You can name the sheet as you wish.

Step 3 - Open the script editor

Next step is to open the scipt editor where we will write the code required for our tool.

Click on Tools -> Script Editor on the menubar. This will open up the script editor in a new tab.

Give the script an appropriate name. This script is linked to the sheet now. All changes saved here will be available later also.

Step 4 - Create files

I have open sourced the code required for the tool at shahidhk/google-sheets-password-manager. You can copy the script files one by one to the script editor window.

Code.gs is the main Apps script file. All functions that interact with the Google Sheets are written here. You can get it from Code.gs on GitHub and paste it into the editor window. You can remove the function that is already present in the editor. Once pasted, press Ctrl+S to save.

Next, create a new file called newEntry.html by going to the File -> New -> HTML File menu. Remove the existing HTML and add code from newEntry.html. Press Ctrl+S to save.

Similarly, create new files and copy contents from decryptPassword.html and styles.html.

We have the following files in our script editor now:

1. Code.gs
2. newEntry.html
3. decryptPassword.html
4. styles.html

Step 5 - Run the script

Open Code.gs and use the Select function dropdown on the toolbar to select the onOpen function. This function is executed everytime the the Google Sheet is opened. Click on the Run button (play icon) next to the dropdown to run the function.

You will now be prompted to grant permissions for the script to access the sheet. If you get a “This app isn’t verified” screen, you can safely over-ride by clicking the “Advanced” link at the bottom as you’re the developer for the app (since you just copy pasted the code).

I’m not saying copy pasting the code is safe, I am assuming you can go through couple of lines of code and figure out if it’s doing something fishy.

Once you allow the permission request and the function is ran (you might need to click the Run button agian), a new menu item will appear on the Sheet.

Step 6 - Add a password

Let’s save a password now. Click on Password Manager -> Add new password. This will open a new dialog box with several input fields.

• Name: Name of the website that you’re adding.
• URL: Login URL for the website.
• Username
• Password
• Shared secret: This is the key used to encrypt the password, you’ll need this key to decrypt it later.
• Re-type shared secret: This is to avoid any typing errors as the input is a password field.

Once all entries are added, click on the Save button.

If there are no errors, the password is encrypted with the secret key (shared secret) and is saved into the sheet.

Encryption happens on the browser. Raw password is not sent to the Sheet.

The password is encrypted using SJCL and upon encrypting a string with a key, it outputs a JSON object:

{
  "iv": "YfYlmYRzJBfo0xFGUNQ8Fg==",
  "v": 1,
  "iter": 10000,
  "ks": 128,
  "ts": 64,
  "mode": "ccm",
  "adata": "",
  "cipher": "aes",
  "salt": "s0McGyx/dmQ=",
  "ct": "Xit1P9/DeedTmGf3WZBT4V+HNHIM3jtV0K8="
}

This object is base64 encoded and saved into the password column.

Each entry can have a different shared secret.

Because each entry can have a different shared secret, you can safely share this across your team and only those who you communicate the shared secret wil be able to decrypt the corresponding password.

You can also choose to have a common shared secret for team-wide sharing.

Step 7 - Decrypt a password

To decrypt a password, bring the focus to any column in the target row and then click on Password Manager -> Decrypt password menu. This will open up the decrypt password dialog box. Enter the shared secret and click the Decrypt button.

This will reveal the decrypted password in the textbox. You can click the Copy to Clipboard button if you need to copy the password.

Feedback

The project is open source at https://github.com/shahidhk/google-sheets-password-manager. Looking forward for your suggestions and contributions.