0

I've seen questions on stackoverflow like this one, but I can't get it to seem to work. I have a sheet that get's duplicated when a certain script is run and that gets deleted when a script is run, but when others try to run it they get the message that they do not have permission to copy the sheet, because of protected ranges and vice versa for deletion. I know this is possible with a web app, but I can't get it to seem to work. The scripts run via an image bound with a script in a sheet. These are my scripts:

Creation of a new sheet:

function dossierAanmaken() {
var response = Browser.msgBox('Dossier Aanmaken', 'Weet je zeker dat je een nieuw dossier wilt aanmaken?', Browser.Buttons.YES_NO);
if (response == "yes") {
  aanmaken();
} else {
  Logger.log('Het aanmaken van het dossier is geannuleerd!');
}
} 

function aanmaken() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("-Dossier");
  var sh2 = ss.getSheetByName("ZZZ - Medewerkers");
  var rg1 = sh1.getRange(2, 7, 1, 1);
  var rg2 = sh2.getRange(sh2.getLastRow() + 1, 1, 1, 1);
  rg1.copyTo(rg2, { contentsOnly: true });
  var sh3 = ss.getSheetByName('-Template');
  var sh4 = sh3.copyTo(ss).setName('Test');
  sh4.activate();
  var cell = sh1.getRange("G2");
  var v1G2 = cell.getValue();
  sh4.setName(v1G2);
  sh4.setTabColor(null);
  Utilities.sleep(200);
  var shts = ss.getSheets().map(sh => sh.getName()).sort().forEach((n, i) => {
    ss.setActiveSheet(ss.getSheetByName(n));
    ss.moveActiveSheet(i + 1)
  });
  var sh5 = ss.getSheetByName('ZZZ - Medewerkers');
  var range = sh5.getRange("A3:A100");
  range.sort({column: 1, ascending: true});

  var copySheet = ss.getSheetByName("-Dossier");
  var source = copySheet.getRange(2,7,5,1);
  var destination = sh4.getRange(2,7,5,1);
  source.copyTo(destination, {contentsOnly:true});
  sh2.hideSheet();
  sh4.showSheet();
  sh4.hideSheet();
  copySheet.activate();

  addtoLogboek();

 }

 function addtoLogboek() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("-Logboek");
  var sh1 = ss.getSheetByName("-Dossier");

  // get source range
  var source = copySheet.getRange(7,1,1,17);
  // get destination range
  var destination = copySheet.getRange(copySheet.getLastRow()+1,1,1,1);

  // copy values to destination range
  source.copyTo(destination);

  var employeeName = sh1.getRange("G2").getValue();
  var sh2 = ss.getSheetByName("-Logboek");
  sh2.getRange("A8:A350").createTextFinder('-').matchEntireCell(true).replaceAllWith(employeeName);


  var rangeList = sh1.getRangeList(['G2:G6']);
  rangeList.clearContent();

  SpreadsheetApp.getUi().alert("Dossier Aangemaakt", "Het dossier is succesvol aangemaakt!", SpreadsheetApp.getUi().ButtonSet.OK);

}

Deletion of an existing sheet:

function dossierVerwijderen() {
var response = Browser.msgBox('Dossier Verwijderen', 'Weet je zeker dat je dit dossier wilt verwijderen?', Browser.Buttons.YES_NO);
if (response == "yes") {
  verwijderen();
} else {
  Logger.log('Het verwijderen van het dossier is geannuleerd!');
}
} 

function verwijderen() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("-Dossier");
  var cell = sh1.getRange("G2");
  var v1G2 = cell.getValue();
  ss.deleteSheet(ss.getSheetByName(v1G2));
  var employeeName = sh1.getRange("G2").getValue();
  var sh2 = ss.getSheetByName('ZZZ - Medewerkers');
  sh2.createTextFinder(employeeName).matchEntireCell(true).replaceAllWith('');


    removefromLogboek();
 }

 function removefromLogboek() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName("-Dossier");
  var employeeName = sh1.getRange("G2").getValue();
  var sh2 = ss.getSheetByName("-Logboek");
  sh2.getRange("A8:A350").createTextFinder(employeeName).matchEntireCell(true).replaceAllWith('VERWIJDEREN_LOGBOEK');

  var rows = sh2.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {

  var row = values[i];

  if (row[0].indexOf('VERWIJDEREN_LOGBOEK') > -1) {
  sh2.deleteRow((parseInt(i)+1) - rowsDeleted);
  rowsDeleted++;
 }
 }

  var rangeList = sh1.getRangeList(['G2:G6']);
  rangeList.clearContent();

  SpreadsheetApp.getUi().alert("Dossier Verwijderd", "Het dossier is succesvol verwijderd!", SpreadsheetApp.getUi().ButtonSet.OK);

 }

0 Answers0