Using onEdit with a Google Sheets shared Spreadsheet that Collects info from one sheet and setsValue in another sheet while limiting the Other users

Question

I have a Time Out/Time In script that I've been working on for a few weeks now. I'm very new so it took a lot of research and help. Now the script is working as intended but I wanted to use Google Sheets specifically to share the sheets with other accounts. The idea is that one computer would be the Host and just have the sheets up for data collection. Another computer would have the, let's call them, Clients click locations via drop down in Column C or type in locations in Column C. The onEdit function only activated if Column C was edited on the ACTIVE sheet.(the sheet intended to be used by clients) After they change anything, onEdit uses if Column C equals certain things then start another function that inserts information into the PASSIVE sheet.(sheet collecting data) The issue I'm facing is that I don't want the Client computer to be able to edit anything in the PASSIVE sheet. One way I did this was by using Google Sheets to just protect the PASSIVE sheet from other users, but when I did this, every time they edited Column C, the function wouldn't play out. Probably because the script isn't able to insert into the protected sheet? I thought what would happen would be that by editing Column C on the client computer, it would also edit Column C on the Host computer and the onEdit function would activate on the Host computer. But apparently onEdit functions only activate if it's manually edited, setting Values via script won't cut it. Am I wrong?

Another thing I did was Hide PASSIVE sheet but keep it Editable, this works out, but they can still just click view and reveal it again. Is there a way to prevent this?

I'm trying to make this script unbreakable from the client computer.

Here's some lines of code to show you what I mean.

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   var row = getCurrentRow()
   var Location = getValue('ACTIVE!C' + row)
   var firstName = getValue('ACTIVE!A' + row)
   var lastName = getValue('ACTIVE!B' + row)
  var passiveRow = findRows(1,firstName,2,lastName,5,"",'PASSIVE');

  if(SpreadsheetApp.getActiveSheet().getName() !== "ACTIVE") return;

  if(Location !== 'OFFICE' && Location !== ""  && passiveRow !== "") {
    timeIn();
    places();

    Logger.log(passiveRow)
}

    else if(Location !== 'OFFICE' && Location !== ""  && passiveRow == "") {
    places();

    Logger.log(passiveRow)
}

     else if(Location === 'OFFICE') {
    timeIn();
}

}

The "findRows" function is the function that finds the row inside the PASSIVE script that matches the criteria and gives me the Row in order to put the TimeIn function.

In the end, I don't care what method is used, maybe there's a better one out there I'm not thinking of. A way to keep the Client from editing the PASSIVE sheet, but they can still Edit the ACTIVE sheet and the code will run properly.

Hink Hink · Accepted Answer · 2019-05-11T20:11:43

We run a similar system at my workplace for sign in / sign out. This how we did it:

Instead of using a simple onEdit() trigger, use an installable edit trigger on the client sheet (via the project current triggers menu option). Do this with your account.
Create a different host spreadsheet. (The client gmail account does not need editing access to the host spreadsheet.)
Have the function on the client sheet append the desired information to the host spreadsheet. Example Code:

    function onEdited() {
      SpreadsheetApp.openById("host spreadsheet id").appendRow(["this function 
      ran"]);
    }

Share the client sheet with the client. When they make changes, your function will run and update the host spreadsheet.

Note if your on edited function requires any user permissions from the client to run (e.g. getting the session information) it will not start, so test with a simple "I ran" message and work your way up to where you need to be.

Using onEdit with a Google Sheets shared Spreadsheet that Collects info from one sheet and setsValue in another sheet while limiting the Other users

1 Answers