Google Sheets Scripts - run scripts as administrator / owner

user_7564774 0
Viewed 167

I have Google Sheet, name TEST https://docs.google.com/spreadsheets/d/1HsRwknyZBmZZ9nibDfNpOwqkVsFGThDyrTwspV-5_4U/edit?usp=sharing

Sheet: Arkusz 1
Column A: all people can edit
Column B: only owner can edit

Library (for everyone): https://script.google.com/macros/s/AKfycbzpnEMhIG-0dMp54q3W4UxoT71-lSdfF7Qxf7rq_j6gJMNIxuCS/exec

A user cannot add a row because it is blocked by column B, which belongs only to the admin. How can I create macro, which allow user to add new rows?

I have three scripts:

function insertRow() { 
var ss = SpreadsheetApp.getActive() 
var sheetName = ss.getActiveSheet().getName() 
var row = ss.getActiveRange().getRow() 
var numRows = Browser.inputBox('Insert Rows', 'Enter the number of rows to insert', Browser.Buttons.OK); 
Logger.log(numRows) 
var url ="https://script.google.com/macros/s/AKfycbzpnEMhIG-0dMp54q3W4UxoT71-lSdfF7Qxf7rq_j6gJMNIxuCS/exec" 
var queryString = "?sheetName="+sheetName+"&rowNo="+row+"&noOfRows="+numRows 
url = url + queryString 
Logger.log(url) 
var request = UrlFetchApp.fetch(url) 
if (request != 'Success') 
Browser.msgBox(request) 
}

Second:

function doGet(e) { 
var param = e.queryString 
var parameters = param.split("&") 
// This just checks only 3 parameters are present else gives a invalid link 
if (param != null && parameters.length == 3){ 
param = e.parameter 
var name = param.sheetName 
var row = Number(param.rowNo) 
var numOfRows = Number(param.noOfRows) 
} else{ 
return ContentService.createTextOutput("Invalid query") 
} 
try{ 
var ss = SpreadsheetApp.openById("https://docs.google.com/spreadsheets/d/1HsRwknyZBmZZ9nibDfNpOwqkVsFGThDyrTwspV-5_4U") 
var sheet = ss.getSheetByName(name) 
sheet.insertRowsAfter(row, numOfRows); 
var source_range = sheet.getRange(row,1,1,sheet.getLastColumn()); 
var target_range = sheet.getRange(row+1,1,numOfRows); 
source_range.copyTo(target_range); 
} 
catch (err){ 
return ContentService.createTextOutput("error: "+err) 
}
return ContentService.createTextOutput("Success") 
}

And after clicked function insertRow and filled number of rows I have doPost(e) information.

Could you help me?

google-apps-script javascript
2 Answers

On the solution you provided below, I see that the issue is in mainScript

function mainScript(e) { 
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
    // assign the sheet to a variable and use it below instead of spreadsheet
    var sheet = spreadsheet.getSheetByName('ZNC')
    sheet.getRange('A2').activate()   
    sheet.insertRowsBefore(sheet.getActiveRange().getRow(), 1); 
}
user_161186860

Hmm, I created solution, but I think there's a bug somewhere, because it doesn't add the line, even though everything is correct and the script is published as public.

function ZNCWiersz() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('ZNC'), true);
  const activeSheet = SpreadsheetApp.getActiveSheet().getSheetName();
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(`${url}?sheetName=${activeSheet}`, {
    headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
  });
// DriveApp.getFiles()  // This is used for automatically detecting the scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for the access token.
}
// When runScript() is run, this function is run.
const doGet = (e) => ContentService.createTextOutput(mainScript(e));
// This script is run by Web Apps.
function mainScript(e) {
  
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
    spreadsheet.getSheetByName('ZNC')
    spreadsheet.getRange('A2').activate()
    spreadsheet.insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
    
}
user_75647740
Related