Google Spreadsheet Row Lock Function

Question

who can help to develop a script that will lock the google spreadsheet row after a user entered data to the table row.

Case Description:

I have a spreadsheet table. This table is used by many users to enter data. I need to be sure that different uses can not change the data entered by the others. The best way if each row will have a special "lock" button. So when the user entered all the info into table row he can push the "lock" button to prevent data changes by other users. Besides I wish the user can change the data he entered but with some limit of time - for example only for at least 30 minutes he locked the row.

As an admin I wish to be able to change any data in a spreadsheet table.

Thank you for help.

Xun Lee Xun Lee · Accepted Answer · 2017-01-22T04:26:59

perhaps you can use google spreadsheet's Protected Range feature as the lock. When person A wanna write data, he set the sheet as private, after that, set public. During person A's writing, if person B want to write also, he will meet exception, he can catch it, and wait a moment, later try to write into.

class ContextManagerUpdateSheet(object):
    def __init__(self, spread_sheet_id, sheet_id):
        self.spread_sheet_id = spread_sheet_id
        self.sheet_id = sheet_id
        # self.end_row_index = end_row_index
        # self.end_column_index = ord(end_column_index) - 65

    def __enter__(self):
       logger.info("set spreadsheet sheet: {sheet_id} protected.")
       self.protected_id = set_protected(self.spread_sheet_id, self.sheet_id)

    def __exit__(self, *others):
       logger.info("release protected spreadsheet sheet: {sheet_id}.")
       delete_protected(self.spread_sheet_id, self.protected_id)


def runner():
   with ContextManagerUpdateSheet("{google_spread_url}", 0):
       from datetime import datetime
       print datetime.now().strftime("%Y-%m-%d %H:%M:%s")
       data = [["www", "3333"]]
       apped_data("{google_spread_url}", 0, data)

－－－

@retry(googleapiclient.errors.HttpError, 5, 20, logger=logger)
def set_protected(spreadsheet_id, sheet_id):
   logger.info("test")
   service = get_service_handler()
   requests_list = list()
   requests_list.append(__protected_info(sheet_id))
   body = {
      "requests": requests_list
   }
   resp = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id,
                                          body=body).execute()
   return resp["replies"][0]["addProtectedRange"]["protectedRange"]["protectedRangeId"]

Google Spreadsheet Row Lock Function

1 Answers