How to protect specific range in google sheets dynamically in app script?

0
votes

I know how to protect ranges in google sheets through app script but I want to protect specific range dynamically how can we do that?

I have tried putting protection when the data comes from BigQuery but then after using filters it is not letting the update the current sheet because its protected

var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1:L28').activate();
  var protection = spreadsheet.getRange('A9').protect();
  protection.removeEditors(['[email protected]']);

I expect the output to be protected only after data reaches to google sheets and then be able to filter the data and changing the data in sheets with no protected sheet error

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

If you Bigquery data is inserted into Google Spreadsheets externally, the update event cannot be detected with Apps Script.

What you can do when you want to insert BigQuery data is:

  1. Remove all the protections
  2. Retrieve data from BigQuery and paste in Spreadsheets with Apps Script
  3. Set a new protection

Sample:

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  //remove all protections
  spreadsheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(function(e){e.remove()});
  //not sure what you want to do with the following line
  spreadsheet.getRange('A1:L28').activate();
  //  Retrieve your Bigquery data and proceed as required
  queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);  
  ...  
  //After finishing the query, protect the range
  //provided you want to protect only cell 'A9' in the active sheet
  var protection = spreadsheet.getActiveSheet().getRange('A9').protect();
  protection.removeEditors(['[email protected]']);
}