CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Iris
Iris

Reputation: 21

Google Apps Script: How to fix "failed while accessing document"? Fails on same line every time

I am by no means an expert - I've been teaching myself Google Apps Script while building this script and I cannot figure out how to fix the issue.

This specific section of my code is filtering a spreadsheet with two separate criteria. The first is filtering by a specific string in column A, the other is filtering by the cell background color in column D. I was using this EXACT code without an issue a few days ago but, as my script grew, the background color filtering is no longer working and throws this error every time:

Exception: Service Spreadsheets failed while accessing document with id [Correct ID]

Here is the code:

function deletecolor() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Master");
  var range = sheet1.getRange(['A:L']);
  var filter = range.createFilter();
  
  var Filter_Depreciated = SpreadsheetApp.newFilterCriteria().whenTextDoesNotContain(["DEPRECATED"]);
  var Filter_Background = SpreadsheetApp.newFilterCriteria().setVisibleBackgroundColor(SpreadsheetApp.newColor().setRgbColor("#ffffff").build());
  filter.setColumnFilterCriteria(1,Filter_Depreciated);
  filter.setColumnFilterCriteria(4,Filter_Background);
}

The exception is thrown on the filter.setColumnFilterCriteria(4,Filter_Background); line every single time. I have about 900 other lines of code in this script all calling on the exact same document and no other line of code fails.

Dear people who know so much more than I do, please help!

Upvotes: 1

Views: 211

Answers (1)

Iris
Iris

Reputation: 21

I had alternating row background colors set on this sheet from the Google Apps Script. When I removed the alternating colors, the script worked.

For anyone who may need the answer in the future, here is my code that is working:

function deletecolor() {
  var rangeBackground = SpreadsheetApp.getActive().getDataRange();
  rangeBackground.getBandings().forEach(banding => banding.remove());

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Master");
  var range = sheet1.getRange(['A:L']);
  var filter = range.createFilter();
  
  var Filter_Depreciated = SpreadsheetApp.newFilterCriteria().whenTextDoesNotContain(["DEPRECATED"]);
  var Filter_Background = SpreadsheetApp.newFilterCriteria().setVisibleBackgroundColor(SpreadsheetApp.newColor().setRgbColor("#ffffff").build());
  filter.setColumnFilterCriteria(1,Filter_Depreciated);
  filter.setColumnFilterCriteria(4,Filter_Background);
}

Upvotes: 1

Related Questions