How to moderate data input into a spreadsheet
A smart engineer suggested a lightweight way of moderating data coming into a Google Sheets spreadsheet. I’m sharing it here in case you find it useful. Note that this is not an official Google solution, and anything on my blog is my own opinion or idea, not Google’s.
This idea came from François Wouts, as a lightweight way of moderating anonymous data coming into Tech Comm on a Map. The data source for Tech Comm on a Map is a Google Sheets spreadsheet. People contribute data entries anonymously from all over the world: conferences, meetups, educational courses, businesses. They enter the data via a web form or via an option in the Android app for Tech Comm on a Map.
It’d be a pity if something weird ended up on the map. I needed some way of moderating the incoming data before it ended up on the map.
The solution comprises two spreadsheets and a formula:
- The first sheet accepts the data entered via the form. Anonymous data comes into this sheet via the web form and the Android app.
- Also on sheet 1 is an extra column containing a value that indicates whether each entry is accepted or not. When entries come into the sheet, the column is empty. When moderating the data, I can mark each entry as accepted or not accepted by entering a value in that column
- The second sheet contains a formula that copies the data from sheet 1. It copies each row where the “accepted” column is set to yes, and ignores the other rows.
- Sheet 2 is protected from public editing. The app draws its data from this sheet.
Here’s the formula that copies the data from one sheet to the other:
=QUERY(ImportRange( "MY-SPREADSHEET-ID" ,
"MY-DATA-ENTRY-SHEET-NAME!B2:K2000" ) ,
"Select * Where Col20 = 'Y' " , 0)
If you’d like to use this technique, create a Google Sheets spreadsheet doc containing 2 sheets:
- Sheet 1 will contain the unmoderated data items. Any web form or app should write to this sheet.
- Sheet 2 is your protected data sheet. This is the sheet from which your app should draw its data. See the Google documentation for help on protecting a sheet.
Then apply the formula to your sheets as follows:
- Copy the above formula.
MY-SPREADSHEET-IDyour own spreadsheet ID. This is the ID of the Google Sheets doc. The ID is a long string of numbers and letters.
MY-DATA-ENTRY-SHEET-NAMEwith the name of your sheet 1. This is the sheet that accepts data entry via a form.
- Replace the cell range
B2:K2000with the location of the columns and rows in sheet 1 containing your data.
- Adjust the column number in
Col20to reflect the number of the column containing your “accepted yes/no” indicator.
- Paste the adjusted formula into the top left cell of sheet 2 in your spreadsheet. This is the protected sheet from which your app should draw its data.
I hope this is useful. I was delighted when François suggested it, because it’s lightweight and simple to implement.