Highlight a name of current user in Google Sheets using Google Apps Script

Question

I'm working on spreadsheet with table that contains names of workers and their availability during whole year. My goal is to highlight cell with proper name when specific person opens the sheet. All users have their google mail in [email protected] schema.

I've already made some code which find name and do bold action on active user's name, you can see it below:

function onOpen(e) 
{
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();
 for(var k = 0; k<sheets.length; k++)
 {
    var data = sheets[k].getDataRange().getValues();
    var user = Session.getActiveUser().getEmail();
    var splitname =  user.split("@")[0];
    var first = splitname.split(".")[0];
    var last = splitname.split(".")[1];
    var formatted_first = first.charAt(0).toUpperCase() + first.slice(1);
    var formatted_last = last.charAt(0).toUpperCase() + last.slice(1);
    var name = formatted_first + " " + formatted_last;

    for(var i = 0; i<data.length;i++)
    {
      if(data[i][0] == name)
      { 
        var a = i+1;
      }
    }
    sheets[k].getRange(a,1).setFontWeight("bold");
  }
}

The problem is that:

I don't know how to perform an action temporarily, only for time when sheets is open.
After writing code I realized that bold action will be visible for all active users, so if one user opens the sheet when the other has it already open and not close yet, he will see not only his name highlighted but also that other user's name.

Soo, my question is whether there is any possibility to make changes visible only for user that made them and how to perform the highlighting only while sheet is open.

If you know any google feature which makes it possible without using the macro, it would be the best solution. Maybe my research wasn't good enough.

Thank you in advance for help!

Google Sheets is a shared resource. So any changes you make to the Google Sheet cannot be displayed only to a single user, with the exception of FilterViews. Consider using them, or a sidebar that allows the user to click a link to set the active cell to the one that is pertinent to their work. — tehhowch
In addition to the above, You'll be better off creating a custom web-app macro. — TheMaster

Rubén Rubén · Accepted Answer · 2019-01-15T00:21:55

Soo, my question is whether there is any possibility to make changes visible only for user that made them and how to perform the highlighting only while sheet is open.

No, there isn't any possibility to do that. The alternatives are to have one spreadsheet for each user or to edit the sharing settings to allow only one user to access the spreadsheet besides the owner but you will have to assume that the owner and the current editor will be able to see the changes made by the other.

NOTE: Filter Views allow users to apply filter settings visible only for the user who is using the filter view but any change made to the filtered data will be viewable by the other users.

Highlight a name of current user in Google Sheets using Google Apps Script

1 Answers