I have a very complicated tool that I developed that produces a mass amount of information. Technically, the whole sheet should be a database drive website. However, it is a spreadsheet:

Spreadsheet as database running a game

The entire tab is protected against modification with the exception of Cell B1-E1 (Merged). This cell uses data validation from the tab "Families!A:A". My problem appears to be that non-english speaking people are playing the game and using some other source for their selection and pasting it into the field. When they paste, it overwrites the formatting and data validation for that field and breaks the sheet.

At least twice a day I have to reset the format for that cell. Is there a way to protect the cell so that they may only select from the drop down?

  • How did you get the combobox in the second sheet? Feb 4 '13 at 18:08
  • It's the same as the first, I added data validation and selected a column from the Cards tab. Normally, it would force uses to ONLY type or select a character from the list, but if they copy and paste from like google translate or another spreadsheet, all formatting is gone, including the data validation.
    – Neobane
    Feb 4 '13 at 20:17
  • via Google Apps Script? Feb 4 '13 at 20:20
  • 1
    No, click on a cell, click on "Data > Validation" menu. Change "Criteria" from numbers to Items from a list. Click in the box next to Create List from Range box and either type in the tab and range or select the cells directly and it should automatically enter the range in the box. The only problem is that if you share it, the users must know not to paste into that field. They either have to type in the text exactly as it's spelled in the range or select it from the drop down.
    – Neobane
    Feb 4 '13 at 20:33

You could tell them to use the paste special option when they paste... Might not be possible in your case but I came across your problem because I was having a similar problem - except that it is a small group of people I know who are doing the pasting so I can give them special instructions...


I see you are no longer maintaining the spread sheet, but I've got a few ideas on how to accomplish were you are trying to do. I hope some of these ideas might be useful!

Reduce the work involved in resetting:

  • Keep a copy of the sheet formatted exactly as you want the one everyone uses, and use the copy to quickly update or delete the original and re-share another copy.
  • you can force drop down lists for all forms of user input & cross your fingers they don't get deleted.
  • You can use the "filter" formula to import your data to another sheet formatted as you would prefer it.
  • You can have a Google script whose trigger is the spreadsheet being opened that resets the original format.
  • you can import it to another spread sheet, with the correct formatting.
  • You can use Microsoft office's excel-web app. The app isn't as robust as Google-sheets, but the difference is that it keeps the protection from the original spreadsheet. So you can edit it on your computer with, say office 2013, and share it publicly. The protection in excel is really superior to Google-sheets.

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.