CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Anne Murphy
Anne Murphy

Reputation: 31

Google Spreadsheet Scripting: Disable Other Users Form Adding, Deleting or Moving a Column

Bear with me, this is my first time coding or using stackoverflow. I've been searching through the Google documentation and various search engines looking for an answer and someone told me to try here.

I have a shared spreadsheet in Google driver and I'm trying to write a script that prevents any other user but myself from adding, deleting or updating a column. This is what I have so far:

function onOpen() 
{
  var ss = SpreadsheetApp.getActive();

  var items = 
  [
    {name: 'Show alert', functionName: 'showAlert'},
  ];

  ss.addMenu('Custom Menu', items);
}

function onEdit(e)
{
   var activeUsersEmail = Session.getActiveUser().getEmail();

   //if (e.eventType == 'INSERT_ROW' || 'UPDATE_ROW' || 'DELETE_ROW')
   //{
     showAlert(activeUsersEmail);
   //}
}

function showAlert(activeUsersEmail) 
{
  if (activeUsersEmail != '[email protected]')
  {
    var result = Browser.msgBox
    (
      'Permission Denied,
      'Please contact Anne Murphy at [email protected] if you wish to update a column.',
      Browser.Buttons.OK
    );

    if (result == 'OK') 
    {
      Browser.Close()
    } 
  }
}

I can't seem to check the event type for those three cases... Also, once I know that I can check the event for add, update or move, how do I stop the user from actually doing it?

Any help would be much appreciated. Sorry again for being so lost.

Upvotes: 3

Views: 12766

Answers (1)

Niccolo
Niccolo

Reputation: 815

In a shared spreadsheet to prevents any other user but myself from adding, deleting or updating a column you don't need a script.

When you say prevent them updating a column that implies you don't want them to have write access. Either this is to a particular column or to any column. You can set protection to one of view only, comment only to;

  • the entire spreadsheet
  • individual sheets or
  • areas within a sheet

If you do any of these they will not be able to delete the column either.

Reading your code, the line;

//if (e.eventType == 'INSERT_ROW' || 'UPDATE_ROW' || 'DELETE_ROW')

Implies you want to check if they have modified your sheet on a row basis.

To prevent them inserting or deleting a row,

  • create a blank column
  • protect the column from editing
  • hide the column

If anyone tries to add or delete a row, this will span the hidden protected column and they will get an error message telling them this.

Now the only onEdit(e) you will detect with be the editing of a cell or group of cells.

Note that adding/deleting rows will not trigger onEdit(), they will trigger onChange(). Within this there is specific way to tell whether a row has been deleted or inserted.

onEdit(e) will allow you to look at the range that has been affected, you can cross check this with your spreadsheets parameters, 

var sheet      = e.source.getActiveSheet();
// the last column of the sheet that contains content
var lastColumn = sheet.getLastColumn();
// Returns the position of the last row that has content  
var lastRow    = sheet.getLastRow();
// Returns the number of columns independently of content in target sheet 
var maxColumn  = sheet.getMaxColumns();     
// Returns the number of rows independently of content in target sheet 
var maxRow     = sheet.getMaxRows();

It was not clear to me form your question and code what exactly you wanted to do. If I have misunderstood please explain and I will amend my help. Assuming this is still of interest and not too late a reply.

Upvotes: 4

Related Questions