Creating One-time Writing Cells using Google Apps Script

24 Jan 2019, 11:37

GasTips

This sample script is for creating one-time writing cells using Google Apps Script. At first, it supposes the following situation.

A Spreadsheet is shared with users. The owner of Spreadsheet is you.
After users put a value to a cell, you don’t want to make users edit the cell again.
- Namely, you want to protect the cell.

This sample script achieves above situation.

Preparation

Before you use this script, please do the following flow.

Prepare a Spreadsheet and open the script editor.
Copy and paste the sample script to the script editor, ans save it.
- Please set ownerEmail. In this sample, it’s your email address.
Enable Sheets API at Advanced Google Services and API console.
- You can see about how to do those at here.
Install a trigger. Please install myFunction() as the OnEdit trigger.
1. Open the script editor.
2. Edit -> Current project’s triggers.
3. Click “Add Trigger”.
4. Set “myFunction” for “Choose which function to run”.
5. Set “From spreadsheet” for “Select event source”.
6. Set “On edit” for “Select event type”.
Share the Spreadsheet with users.

The preparation was done.

Sample script

function myFunction(e) {
  var ownerEmail = "#####"; // Please set owner's email.

  // This method is from https://gist.github.com/tanaikech/95c7cd650837f33a564babcaf013cae0
  var a1notation2gridrange1 = function (sheetid, a1notation) {
    var data = a1notation.match(/(^.+)!(.+):(.+$)/);
    var ss = SpreadsheetApp.openById(sheetid).getSheetByName(data[1]);
    var range = ss.getRange(data[2] + ":" + data[3]);
    var gridRange = {
      sheetId: ss.getSheetId(),
      startRowIndex: range.getRow() - 1,
      endRowIndex: range.getRow() - 1 + range.getNumRows(),
      startColumnIndex: range.getColumn() - 1,
      endColumnIndex: range.getColumn() - 1 + range.getNumColumns(),
    };
    if (!data[2].match(/[0-9]/)) delete gridRange.startRowIndex;
    if (!data[3].match(/[0-9]/)) delete gridRange.endRowIndex;
    return gridRange;
  };

  var sheet = e.source;
  var spreadsheetId = sheet.getId();
  var sheetName = sheet.getSheetName();
  var range = e.range.getA1Notation();
  var rng = range.indexOf(":") > -1 ? sheetName + "!" + range : sheetName + "!" + range + ":" + range;
  var protectedRange = Sheets.Spreadsheets.get(spreadsheetId, {ranges: rng, fields: "sheets/protectedRanges"});
  if (!protectedRange.sheets[0].protectedRanges) {
    var resource = {"requests": [{"addProtectedRange": {"protectedRange": {
      "range": a1notation2gridrange1(spreadsheetId, rng),
      "editors": {"users": [ownerEmail]},
    }}}]};
    Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
  }
}

Note

In this sample, when users put a value of a cell in the shared Spreadsheet, the cell is automatically protected. By this, users cannot edit the cell again.
Owner always can edit all cells.
This is a simple sample script. So please modify it to your situation.

For example, you can also achieve as following situations by modification.

Select the ranges and sheets you want to protect.
Select the editor users.
Set the number of edit.
etc..

Creating One-time Writing Cells using Google Apps Script

Preparation

Sample script

Note

References

Share!