Run Google Sheet Add-On as Admin

Question

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 on the Add-On.

Admin user is Owner of all three files. Tech user doesn't have access to Manager SSheet. All the code is placed in Admin Sheet and Add on is created. So here I have to call code in Admin Sheet from Tech Sheet/user.

I created Add-On but it is taking Tech Sheet as reference. Since Tech Sheet do not have permission to Manager Sheet, copy data is failing.

Is there any possibility to run Add-on as Admin without providing permission to Tech user?

Answer 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

function onInstall(e) {
  onOpen(e);
}

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

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

function getService() {
  var PRIVATE_KEY ="-----BEGIN PRIVATE KEY-----  XXX_YOUR_KEY_XXX  -----END PRIVATE KEY-----\n";
  var CLIENT_EMAIL = '[email protected]'; 
  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');
}

function copySheet(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);
      }
  }