5

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?

aynber
  • 22,380
  • 8
  • 50
  • 63
Mike Eburne
  • 351
  • 2
  • 7
  • 21

1 Answers1

6

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

Jack Brown
  • 5,802
  • 2
  • 12
  • 27
  • When I run the WebApp code to authorize I get an error with the line containing 'var param = e.queryString'. – Mike Eburne Mar 16 '17 at 08:23
  • Yep that is normal – Jack Brown Mar 16 '17 at 08:24
  • Error is since you are not passing any parameter called e to it yet – Jack Brown Mar 16 '17 at 08:25
  • When you ran it for first time, did you get a dialog box asking you to give permission to edit google sheets? If yes, then it was authorized. – Jack Brown Mar 16 '17 at 08:31
  • The script is failing. I get the message generated by the 'request != 'Success' at the end of my function. How do I post my code so you can see it? – Mike Eburne Mar 16 '17 at 08:31
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/138191/discussion-between-user2269532-and-jagannathan-alagurajan). – Mike Eburne Mar 16 '17 at 08:36
  • my request isn't fetching the web app (y tested de url string and is correct), but the request ever returns ` Sign in - Google Accounts – Trimax Jan 08 '20 at 11:08
  • 1
    @Trimax I believe the issue is, the web app needs to be setup to run even for anonymous users. – Jack Brown Jan 10 '20 at 19:40
  • I made the parameter split like this: `var parameters = {}; param.split("&").map(s => s.split('=')).map(a => parameters[a[0]] = a[1].replace('%40', '@')); // '@' get to this point as '%40', encodeURI/decodeURI do not change it. console.log('parameters', parameters);` – Chesare Jan 30 '23 at 18:21