Comparing data across multiple Google Sheets can be tricky—especially when you rely on everything being perfectly aligned. While Excel has a native comparison tool, Google Sheets doesn’t come with a built-in feature to highlight discrepancies. In this guide, we’ll walk you through how to build a simple, custom Google Apps Script app to compare multiple spreadsheets. You’ll be able to spot tab mismatches, column differences, or formula variations in just a few clicks.

If you’d rather watch a quick walkthrough, check out the original video here.

Why Compare Multiple Sheets?

• Consistency: Ensuring your tab names, column headers, and formulas are uniform is crucial for automated scripts that reference your data.
• Collaboration: When multiple people edit similar spreadsheets, it's easy for naming conventions and column headers to drift apart.
• Version Control: If you keep separate copies for different projects or clients, you can quickly confirm that each file has the latest updates.

1. Overview of the Custom Comparison Script

This custom tool uses a Google Apps Script Web App to scan each spreadsheet and report any differences. Here’s the general workflow:

1. Configuration: You provide the spreadsheet IDs you want to compare.
2. Select a “Main” Sheet: Choose which sheet is your “source of truth.”
3. Run the Comparison: The script goes through each selected spreadsheet and compares everything—tab names, column headers, and (optionally) formulas.
4. View Discrepancies: A dashboard highlights missing tabs, differently named columns, or mismatched formulas.
5. Make Fixes: Update the problematic spreadsheet(s) to match the “Main” one.
6. Re-run Comparison: Confirm that all discrepancies have been fixed.

2. Setting Up the Script and Dashboard

A. Prepare Your Sheets

1. Identify the Main Spreadsheet: Decide which file has the correct structure, formulas, and naming conventions.
2. Collect Spreadsheet IDs: Each Google Sheet has a unique ID in its URL (the long string between /d/ and /edit). You’ll need these IDs to feed into the script.

B. Deploy the Apps Script Web App

1. Create a New Script: In your Google Drive, create a new Apps Script project.
2. Paste the Provided Code: Add the code from the repository (or from the script example in the video/transcript) into your Code.gs file.
3. HTML Interface (Optional): For a more user-friendly interface, you can include an HTML file that serves as a dashboard. Label it something like dashboard.html and link it in your main script.
4. Publish the Web App: Go to Deploy > New Deployment, select Web app, and choose Anyone with the link (if you want to allow broader access). Copy the Web App URL for future use.

3. Running a Comparison

1. Open the Web App URL: Once you’ve deployed, visit the link from any browser.
2. Add Your Spreadsheet IDs: In the dashboard or configuration area, input the IDs for the sheets you want to compare.
3. Select the Main Sheet: Mark which spreadsheet serves as your template or “source of truth.”
4. Check Discrepancies: Start the comparison process. The script might take some time if you have large or multiple spreadsheets.

What the Script Checks

• Missing Tabs: If a tab exists in the Main spreadsheet but not in another, it will flag this difference.
• Mismatched Tab Names: If the same tab is spelled differently (e.g., “Expenses Summary” vs. “Expense Summary”), you’ll get a mismatch.
• Column Header Differences: Column A in the Main spreadsheet might be “Project Name,” while another calls it “Project Title.”
• Formula Discrepancies: For each row, the script can check if the formulas match. (By default, it only checks formulas, but you can modify it to compare values as well.)

4. Fixing Discrepancies

1. Follow the Flags: The dashboard will show you the spreadsheet, tab, column, and formula where a mismatch occurs.
2. Open the Target Spreadsheet: Rename tabs, copy columns, or paste formulas from the Main sheet.
3. Drag Down Formulas Correctly: If you’re updating formulas, be sure to apply them to all relevant rows.
4. Re-run the Comparison: Once corrected, run the script again to confirm you’re all set.

5. Tips and Next Steps

• Performance: Large spreadsheets or multiple comparisons may slow down the script. Be patient or consider limiting how many sheets you compare at once.
• Customization: If you want to compare values (like data entries) instead of (or in addition to) formulas, adjust the script accordingly.
• Collaboration: Share the Web App URL with team members who also need to ensure spreadsheets remain consistent.
• Further Automation: Use triggers or scheduled scripts to regularly check for discrepancies and send alerts—ideal for big teams or mission-critical data.

Conclusion

Keeping multiple Google Sheets perfectly in sync can save you from broken formulas, missing tabs, and countless headaches. By using a custom Apps Script, you can systematically check for these discrepancies and fix them before they cause problems. If you have any questions or want to see specific updates to the script, feel free to leave a comment or reach out!

For a more visual walkthrough, don’t forget to watch the original video. Good luck, and happy comparing!