Google Apps Script: Temporarily run script as another user

Question

I have a Google Spreadsheet containing a number of sheets, each or which I have protected using the "Except certain cells' routine in order to protect shared users from editing areas they shouldn't.

I also have a function to insert rows but this obviously fails when running as a shared user due to the protection.

The Insert Rows function works 100% with no protection added.

How can I temporarily change the active user to 'me' while the script is running and then 'remove me' when completed leaving the sheet in its original state including all the protection?

Jack Brown Jack Brown · Accepted Answer · 2017-03-15T20:46:42

The most viable option is to have a web App to insert the row where desired. You can pass the sheet Name and row No via a get or post method. The below method uses a get method. Your google script would look something like this:

function insertRow() {
  var ss = SpreadsheetApp.getActive()
  var sheetName = ss.getActiveSheet().getName()
  var row = ss.getActiveRange().getRow()
  // Below URL is the link to  web App, you will generate it by publish >Deploy as web app. Code for Web app is below. 
  var url ="Put the generated URL here"
  var queryString = "?sheetName="+sheetName+"&rowNo="+row
  url = url + queryString
  Logger.log(url)
  var request = UrlFetchApp.fetch(url)
  if (request != 'Success')
    Browser.msgBox("Sorry Unable to insertRow")
}

Next, you will deploy a web App which will process the get request and add new rows to the sheet. The code would look like this:

function doGet(e) {
  var SheetID = "Your Sheet ID here"
  var param = e.queryString
  var parameters = param.split("&")
  // This just checks only 2 parameters are present else gives a invalid link
  if (param != null && parameters.length == 2){
    param = e.parameter
    var name = param.sheetName
    var row = Number(param.rowNo)
    } else {
    return ContentService.createTextOutput("Failed")
    }
  try{  
     var ss = SpreadsheetApp.openById(sheetID)  
     var sheet = ss.getSheetByName(name)
     sheet.insertRowAfter(row)
     sheet.getRange(row + 1,1).setValue("Inserted Row")
     }
     catch (err){
      return ContentService.createTextOutput("Failed")
     }
 return ContentService.createTextOutput("Success")
}

Make sure to run the webApp once to give it authorization/permission to edit your google sheets. Since the webApp will run under your authorization, it would be able to insert Row without an issue of permission. Also, you can add a unique ID(Email ID of the effective User?) to the get request to make sure only valid request from within the google sheets scripts is processed.

If you would like more details on the web app you can find it here: https://developers.google.com/apps-script/guides/web

Google Apps Script: Temporarily run script as another user

1 Answers