Staying on top of all your passwords can feel like herding cats. With so many to remember from different sites, it’s easy to get overwhelmed. That’s where a password manager comes in handy. Google Sheets is a better way to create a customized and handy password manager.
Why Should You Have a Password Manager?
Having a password manager is like keeping all your keys in one safe spot so you can easily unlock any door without worrying about losing them. Today, we all use many websites, and each one needs a password. Trying to remember everyone—especially since they all need to be different and hard to guess—can be too much.
A password manager does all this hard work for you. It keeps all your passwords locked up but makes it easy for you to access them whenever you need them. It can also create strong passwords for you, which makes your accounts safer from hackers. So, it saves you from the headache of remembering each one and protects your information online.
Step 1: Open Google Sheets
First, go to Google Sheets and log into your Google account. Click on the option to start a new blank sheet. You can name this sheet something easy to remember, like “Password Manager.”
Step 2: Organize Your Columns
Your new sheet will need some headings. At the top of each column, type in the following:
- Website
- URL
- Username
- Password
- Email Used
- Security Questions
- Last Updated
These will be the categories you’ll fill with your respective details.
Step 3: Enter Your Information
Begin adding the details of your various accounts. Since this isn’t encrypted like a dedicated password manager, be cautious about the detail you put in, or consider using a system of hints that only you would understand.
Step 4: Secure Your Information
It’s time to lock down your password manager for extra security. If some details are very sensitive, click and drag to highlight those cells. Right-click, head to ‘Protect range,’ and decide who can edit these cells.
Step 6: Smarten Up With Formulas
Google Sheets can do some of the work for you with formulas. For instance, putting =TODAY() in the ‘Last Updated’ column will keep that date current, helping you remember when a password was last changed.
Step 7: Generate Passwords In Google Sheets with Apps Script (Optional)
To automatically generate a password, you have to open and use App Script. Click Extension > Apps Script.
Once you click the Apps Script tab, a new tab will open with an empty project:
In this empty project, add the following functions:
Use the function below to get the Password Generator tab in the menu section of Google Sheets:
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Password Generator’)
.addItem(‘Generate Password’, ‘generatePassword’)
.addItem(‘Copy Password’, ‘copyPassword’)
.addToUi();
setupSheet();
Use the function below to automate the labels we added in the Password Generator sheet:
function setupSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.clear(); // Clear the sheet to start fresh
// Set up labels
sheet.getRange(‘A1’).setValue(‘Length of Password’).setFontWeight(‘bold’);
sheet.getRange(‘A2’).setValue(‘Include Uppercase Letters (Yes/No)’).setFontWeight(‘bold’);
sheet.getRange(‘A3’).setValue(‘Include Lowercase Letters (Yes/No)’).setFontWeight(‘bold’);
sheet.getRange(‘A4’).setValue(‘Include Numbers (Yes/No)’).setFontWeight(‘bold’);
sheet.getRange(‘A5’).setValue(‘Include Special Characters (Yes/No)’).setFontWeight(‘bold’);
sheet.getRange(‘A6’).setValue(‘Generated Password’).setFontWeight(‘bold’);
// Set up default input values
sheet.getRange(‘B1’).setValue(12);
sheet.getRange(‘B2’).setValue(‘Yes’);
sheet.getRange(‘B3’).setValue(‘Yes’);
sheet.getRange(‘B4’).setValue(‘Yes’);
sheet.getRange(‘B5’).setValue(‘Yes’);
// Set up buttons
createButton(sheet, ‘A7’, ‘Generate Password’, ‘generatePassword’);
createButton(sheet, ‘A8’, ‘Copy Password’, ‘copyPassword’);
}
Use the function below to set the characters that will be used in generating the password:
function generatePassword() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var length = parseInt(sheet.getRange(‘B1’).getValue());
var includeUppercase = sheet.getRange(‘B2’).getValue().toLowerCase() === ‘yes’;
var includeLowercase = sheet.getRange(‘B3’).getValue().toLowerCase() === ‘yes’;
var includeNumbers = sheet.getRange(‘B4’).getValue().toLowerCase() === ‘yes’;
var includeSpecial = sheet.getRange(‘B5’).getValue().toLowerCase() === ‘yes’;
var characters = ”;
if (includeUppercase) characters += ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’;
if (includeLowercase) characters += ‘abcdefghijklmnopqrstuvwxyz’;
if (includeNumbers) characters += ‘0123456789’;
if (includeSpecial) characters += ‘!@#$%^&*()_+[]{}|;:,.<>?’;
if (characters === ”) {
SpreadsheetApp.getUi().alert(‘Please select at least one character type.’);
return;
}
var password = ”;
for (var i = 0; i < length; i++) {
password += characters.charAt(Math.floor(Math.random() * characters.length));
}
sheet.getRange(‘B6’).setValue(password);
}
Once you’ve input the above functions, click the Save icon and Run.
After you click Run, go back to the spreadsheet. You’ll see the Password Generator tab and the options to generate your password, which you can change.
You can test it by modifying the answers in the column B. Then, click Generate Password, and the random password will appear right beside the Generated Password category:
Convert Your Formulas to Text
To save your formulas, let’s start converting them to values. To help you do that easily, install the FileDrop add-on via Google Workspace Marketplace.
Go to the ‘Extensions’ menu at the top of your Google Sheets interface, and click on ‘FileDrop.’ If you want to convert only a specific formula or selection of formulas to values, first select the cells containing those formulas.
With the cells selected, choose the ‘Save selected as text’ option from the FileDrop menu. The selected formulas will convert into text, preserving their output as static values.
To check that the action has been carried out, click on one of the cells where the formula used to be. You should see that the cell now displays only the result, not the formula.
This FileDrop feature is particularly useful for sharing your spreadsheet with others without giving them access to edit the underlying formulas or when you need to ensure the data remains unchanged.
Get a Free Password Manager Template
Get a copy of the free Password Manager Template. I’ve populated some cells as examples, but you can customize them as needed.
Final Thoughts
Creating a password manager in Google Sheets is a straightforward way to keep track of all your login information in one place. Customizing the template to fit your needs and taking precautions to protect your data can make password management a breeze. while a Google Sheets password manager can be very useful, it doesn’t replace dedicated password management apps’ encryption and security features. Use this solution as a personal, easily customizable option, especially if you’re comfortable with spreadsheet software.
Frequently Asked Questions
How can I make my password manager sheet more secure?
To make your password manager sheet more secure, use Google’s two-factor authentication for your account, limit sharing settings to ‘view only‘ or do not share it, and consider encrypting passwords before entering them into the sheet. Additionally, regularly change your Google account password and keep the spreadsheet up to date with security settings.
Can I access my Google Sheets password manager offline?
Yes, Google Sheets provides an offline feature. You must enable offline mode in your Google Drive settings to view and edit your password manager spreadsheet without internet access, ensuring you can access your passwords anytime.
How can I find specific account information easily with formulas?
Use =VLOOKUP() or =HLOOKUP() to quickly locate specific account details. For instance, =VLOOKUP(“AccountName,” A2:D100, 3, FALSE) would search for the account name in the first column of the range A2:D100 and return the value from the third column of the matching row, such as the user’s password.
