I have a Google sheet which is protected with a script to copy-paste a line that contains unprotected cells.
My script is a deployed script i.e. a Web App script.
I want the pasted line to include the same unprotected cells. To do so I get all the unprotected ranges of the sheet , loop over this array and if the unprotected range is in the original line, add to the array a new range that is supposed to be at the right place after the original line has been copied.
When trying to reset the unprotected ranges, the script fails with that error
27 oct. 2023, 07:58:25 Erreur Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Protection.setUnprotectedRanges. at AddNewParticipant(WebAppScripts:260:14) at myMacros(WebAppScripts:11:8) at doGet(WebAppScripts:2:20)
Here is my script
const doGet = e => myMacros(e)
function myMacros(e){
switch (e.parameter.what){
case "Participant":
Logger.log('case is Participant')
AddNewParticipant(e)
break
default:
Logger.log('Exiting with no selection')
}
}
function AddNewParticipant(e){
var ss = SpreadsheetApp.getActiveSpreadsheet()
Logger.log('entering the script')
if (!e) {
const webAppsUrl = "https://script.google.com/macros/s/AKfycbzDV97QE4JL2yI1kTDv2fGTXc7hfDdsFEv1LCs74LVe/dev"; // Please set your Web Apps URL.
UrlFetchApp.fetch(`${webAppsUrl}?sheetName=${ss.getActiveSheet().getSheetName()}&what=Participant`, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
Logger.log('Exit the first if test bloc')
return;
}
var thisSheet = ss.getSheetByName(e.parameter.sheetName)
Logger.log("Exécution sur la feuille "+thisSheet.getName())
var tr =thisSheet.getRange("ParticipantTemplateLine").getRow()//tr for template row
var mc =thisSheet.getRange("MarkColumnRange").getColumn()
var epl = thisSheet.getRange("EndParticipantLine").getRow()
var participantRange = thisSheet.getRange("ParticipantChoisi")
var participant = participantRange.getValue()//the name of the participant to be added
var protection = thisSheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0]
var initiallyUnprotected = protection.getUnprotectedRanges();
var unprotected =[]
for (var i=0; i<initiallyUnprotected.length;i++){
r= initiallyUnprotected[i] // a range
Logger.log(r.getA1Notation())
unprotected.push(r) //keep this range as unprotected
var rStartRow=r.getRow()
var rEndRow=r.getLastRow()
if (rStartRow == tr && rEndRow == tr){ //tr is templateLine i.e. template row
//the range is fully in the template row
//add a similar range in the copy of tr (the copy will be inserted before line epl)
unprotected.push(thisSheet.getRange(epl, r.getColumn(),1,r.getLastColumn()-r.getColumn()+1))
}
}
//here we should see initially unprotected ranges and added unprotected ranges
Logger.log("hereafter modified unprotected ranges")
Logger.log(unprotected)
var plistRange = ss.getRange("PersonList")
var plist =plistRange.getValues()
//Logger.log(participant)
var i=0
for (i=0; i<plist.length;i++){
if( plist[i][0] === participant){
break
}
}
var memberShip = plist[i][1]
//Logger.log( 'memberShip : '+memberShip)
var sourceRange=thisSheet.getRange(tr,1,1,thisSheet.getMaxColumns()).activate()
var testCol= thisSheet.getRange(1,2,thisSheet.getMaxRows()).getValues()
var ret=0
for (var i=0;i<testCol.length;i++){
if (testCol[i] == "◇"){
break
}
ret=i+2
}
//Browser.msgBox('ret is '+ret)
var destRange=thisSheet.getRange(ret,1).activate()
thisSheet.insertRowsBefore(thisSheet.getActiveRange().getRow(),1)
sourceRange.copyTo(destRange,SpreadsheetApp.CopyPasteType.PASTE_NORMAL,false)
//reset values
thisSheet.getRange(ret,2).activate().setValue("")
testLine = thisSheet.getRange(ret,1,1,thisSheet.getMaxColumns()).getValues()
thisSheet.getRange(ret,mc).setValue(participant)
thisSheet.getRange(ret, mc+1).setValue(memberShip)
var c=mc+5
while (c<testLine[0].length){
if (testLine[0][c] == "Oui" || testLine[0][c] == "Non"){
//add 1 to c because column numerotation starts at 1
thisSheet.getRange(ret,c+1).activate().setValue("--")
}
c++
}
//verify unprotected before using it
Logger.log("this is [unprotected] before using it ")
Logger.log(unprotected)
//reset the now unprotected ranges in the protected sheet
protection.setUnprotectedRanges([unprotected])
Logger.log("exiting the function AddNewParticipant")
}
// ### Please don't remove the following comment line.
// DriveApp.getFiles(); // This comment line is used for automatically detecting a scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for requesting Web Apps.
// ###
and hereafter is the log
27 oct. 2023, 07:58:23
Infos
case is Participant
27 oct. 2023, 07:58:23
Infos
entering the script
27 oct. 2023, 07:58:23
Infos
Exécution sur la feuille Modèle événement
27 oct. 2023, 07:58:24
Infos
B2
27 oct. 2023, 07:58:24
Infos
B7:E8
27 oct. 2023, 07:58:24
Infos
E12
27 oct. 2023, 07:58:24
Infos
H12
27 oct. 2023, 07:58:24
Infos
F12
27 oct. 2023, 07:58:24
Infos
H10:I10
27 oct. 2023, 07:58:24
Infos
I8:I9
27 oct. 2023, 07:58:24
Infos
C12
27 oct. 2023, 07:58:24
Infos
hereafter modified unprotected ranges
27 oct. 2023, 07:58:24
Infos
[Range, Range, Range, Range, Range, Range, Range, Range, Range, Range, Range, Range]
27 oct. 2023, 07:58:25
Infos
this is [unprotected] before using it
27 oct. 2023, 07:58:25
Infos
[Range, Range, Range, Range, Range, Range, Range, Range, Range, Range, Range, Range]
27 oct. 2023, 07:58:25
Erreur
Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Protection.setUnprotectedRanges.
at AddNewParticipant(WebAppScripts:260:14)
at myMacros(WebAppScripts:11:8)
at doGet(WebAppScripts:2:20)
I don't understand why the setUnprotectedRanges which is expecting an array of ranges complains here.
I need help.
SetUnprotectedRanges expects a 1D array. The script is sending a 2D one, which causes internal type casting. Replace
with