CODEWITHSUNDEEP
google-apps-script
Muhamed Hazem
Muhamed Hazem

Reputation: 1

I want to create google script to show only the active user data in the spreadsheet

I want to create a google script to show the data of the active user logging in the spreadsheet only because the sheet contains a lot of users and it's a confidential data so how can I do that?

function getActiveUser() { 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var startRow = 2; // First row of data to process 
  var numRows = 31; // Number of rows to process 
  // Fetch the range of cells A2:B8 
  var dataRange = sheet.getRange(startRow, 1, numRows, 7); // Fetch values for each row in the Range. 
  var data = dataRange.getValues(); 
  var email = Session.getActiveUser().getEmail(); 
  Logger.log(email); 
}

Upvotes: 0

Views: 170

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

If the sheet is only supposed to be read rather than edited by the users, you can link the script to an empty Spreadsheet, instead of the Spreadsheet containing all the user data, and modify the script in such a way that it copies from the Spreadsheet containing all the data only the user data into the User Spreadsheet. It would look like this:

function getActiveUser() { 
  var sheet = SpreadsheetApp.openById('your_Spreadsheet_ID').getActiveSheet(); 
  var startRow = 2; // First row of data to process 
  var numRows = 31; // Number of rows to process 
  // Fetch the range of cells A2:B8 
  var dataRange = sheet.getRange(startRow, 1, numRows, 7); // Fetch values for each row in the Range. 
  var data = dataRange.getValues(); 
  var email = Session.getActiveUser().getEmail(); 
  //define a sheet where to copy only the user data
  var userSheet=SpreadsheetApp.getActiveSheet(); 
  var userRange = userSheet.getRange(startRow, 1, numRows, 7);
  // define the column in the sheet where the emails of the users is stored
  var userColumn=1;
  var k=1;
  //Copy all the rows that contain data of the user
  for(var i=0; i<data.length;i++){
    if(data[i][userColumn-1]==email){
      for(var j=0;j<6;j++){
       userRange.getCell(k,j+1).setValue(data[i][userColumn-1+j])
      }
    k++;  
    }
  }  
}

However, keep in mind that this only works if you store the Spreadsheet on a shared drive. So, even if the user will not see the spreadsheet while running the script, he might still access it manually from the shared drive. To solve this problem you would need to write an API and let it run by a Service Account.

Upvotes: 1

Related Questions