0

I’m trying to figure out a solution, most likely by combining multiple Google Sheets, for creating an accessible user database that also protects anonymity. Here’s what I have/need

  1. Right now, the user database auto populates from a Google form (survey) online that people manually fill out. It includes name, phone number, email, and a variety of survey questions. Different projects have their own form and each form populates its own sheet

  2. I want to combine all the data in a publically accessible/editable spreadsheet that does not have user names, emails or other identifying info

  3. Each row (user) is continually updated with new columns as the user takes actions or we contact them. Different sheets will not always have the same columns (though they could)

  4. If i use the IMPORTRANGE function to combine sheets, I'm worried it wont work as i move around and add columns from different sheets feeding into the single public one.

I would use Airtable or some other typical user database, but I want it to be easily edited/viewed by non data scientists, so a more traditional database wont work.

Is there a way to dynamically combine sheets? or use one sheet that can protect/hide some columns?

Thanks and let me know if i can make this question more clear.

1

Is there a way to dynamically combine sheets?

Yes, there is. Use the array handling features of Google Sheets.

or use one sheet that can protect/hide some

Sheet/range protection doesn't hide sheets/columns, just makes that only authorized editors are able to edit them. Hiding a column a sheet/column is not a secure way to hide data from editors as they could make a copy of the spreadsheet and un-hide the hidden sheets/columns.

Example

Let say that you have a spreadsheet with three sheets named Sheet 1, Sheet 2 and Sheet 3 respectively. Sheet 1 and Sheet 2 have the same structure, first row has headers and they have the same columns in exactly the same order, the first column is never empty for actual records (rows with data); we want to join the data of both sheets on Sheet 3.

On 'Sheet 3'!A1 add the following formula:

={'Sheet 1'!A1:J1;FILTER('Sheet 1'!A2:J',LEN('Sheet 1'!A2:A)>0);FILTER('Sheet 2'!A2:J',LEN('Sheet 2'!A2:A)>0)}

where

  • 'Sheet 1'!A1:J1 adds the column headers
  • FILTER('Sheet 1'!A2:J',LEN('Sheet 1'!A2:A)>0) adds the data from Sheet 1
  • FILTER('Sheet 2'!A2:J',LEN('Sheet 2'!A2:A)>0) adds the data from Sheet 2
  • i want to make sure im using the right explanation here: when i say sheet, i mean an entirely different file. i need to make sure that the file with identifying information cannot be read by other editors in the public spreadsheet. this technique works for combining different files? – tom Oct 18 '18 at 21:43
  • @tom you could use IMPORTRANGE on Sheet 1 and Sheet 2 to pull data from Spreadsheet 1 and Spreadsheet 2 respectively – Rubén Oct 18 '18 at 21:44
  • This was helpful! But, what if sheet 1 and 2 have different columns? Different speadsheets will update dynamically and add columns as necessary. I tried out your suggestion, it worked, but then i added a column to sheet 2 and it gave me a value error – tom Oct 19 '18 at 2:18
  • @tom Consider to use Apps Script or a DBMS. Note: It's possible to elaborate a formula based solution but there are a lot of caveats which makes the question too broad for this site. If you still want to go this way, consider to split your project in smaller chunks and if you need help with them post more specific questions. – Rubén Oct 19 '18 at 2:24
  • that's a good suggestion! I'm not familiar with app scripts for google docs, so before I go down that road, do you think it would be possible to do this with a script? And, would it also work on mobile? Thanks again – tom Oct 19 '18 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.