Run Google Sheet Add-on As Admin

I have three spreadsheets (Files) with three different users. Tech SSheet, Admin SSheet and Manager SSheet. Tech SSheet data should be sent to Manager SSheet when Tech user click o

Solution 1:

Yes, there is a possibility to do it - with a service account.

Workflow

• Create Google Service account, enable domain-wide delegation and provide it with the necessary scopes
• Share your Manager SSheet with the service account
• Write an Apps Script function to obtain an Access Token for the service account
• Use this token for the Sheets API to copy data into the Manager SSheet

This allows the user to pass data from his spreadsheet to your spreadsheet to which the user does not have access, but the service account has.

Below is a script for an Add-on that will format data from user´s spreadsheet into to CSV and paste them into Manager SSheet with spreadsheets.batchUpdate

functiononInstall(e) {
  onOpen(e);
}

functiononOpen(e) {
SpreadsheetApp.getUi()
      .createMenu('Copy sheet')
      .addItem('Copy now', 'run')
      .addToUi();
}

functionrun() { 
  var service = getService();
  if (service.hasAccess()) {
    copySheet(service);  
  } else {
    Logger.log(service.getLastError());
  }
}

functiongetService() {
  varPRIVATE_KEY ="-----BEGIN PRIVATE KEY-----  XXX_YOUR_KEY_XXX  -----END PRIVATE KEY-----\n";
  varCLIENT_EMAIL = 'XXXX@XXXX-XXXX.iam.gserviceaccount.com'; 
  return OAuth2.createService('myServiceAccount')
      .setTokenUrl('https://oauth2.googleapis.com/token')
      .setPrivateKey(PRIVATE_KEY)
      .setIssuer(CLIENT_EMAIL)
      .setPropertyStore(PropertiesService.getScriptProperties())
      .setScope('https://www.googleapis.com/auth/spreadsheets');
}

functioncopySheet(service){  
  var sheet=SpreadsheetApp.getActive().getActiveSheet();
  var valueRange=sheet.getDataRange().getValues();
  var data='';
  for(var i=0;i<valueRange.length;i++){
   for(var j=0;j<valueRange[0].length;j++){
    data+=(valueRange[i][j]+",");
    }
   data+=("\n")
  }  
  var dId='ID_OF_Manager SSheet';      
  if(PropertiesService.getScriptProperties().getKeys().length==0){
      PropertiesService.getScriptProperties().setProperty('i', '2');
     }   
  var i=parseInt(PropertiesService.getScriptProperties().getProperty('i'));
  var url='https://sheets.googleapis.com/v4/spreadsheets/'+dId+':batchUpdate';
  var body = {requests: [
            {
                "addSheet": {
                  "properties": {
                  "sheetId": i
                  } 
               }
             },{
                "pasteData": {
                  "data": data,
                  "type": "PASTE_NORMAL",
                  "delimiter": ",",
                  "coordinate": {
                  "sheetId": i,
                  "rowIndex": 0,
                  "columnIndex": 0
                   }
                  }
                }
              ]
             };
      
  var options = {
   "method":"post",
   "muteHttpExceptions": true,
   "headers": {
      "Authorization": "Bearer " + service.getAccessToken()
     },
   "contentType": "application/json", 
   "payload": JSON.stringify(body)
   }      
  var r = UrlFetchApp.fetch(url,options);      
  if(r.getResponseCode()==200){
        PropertiesService.getScriptProperties().setProperty('i', i+1);
      }
  }

Solution 2:

On G Suite document editors, including Google Sheets, if the documents are on "My Unit" and not on a "Shared Unit" (formerly "Team Drive") the permissions roles are

• Owner
• Editor
• Commenter
• Viewer

In other words, there is no "admin", by the other hand, permissions are granted to users not to "sheets".

References

• Share files from Google Drive