While writing a Google Apps Script for Google Sheets to delete Named Ranges that have been orphaned by deleting the target they reference, an unexpected "server error" occurred. Can someone please tell me how to avoid it?
EDIT: Please note that in addition to solving the attached demonstration script, I seek a general solution for every time the routine SpreadsheetApp.getNamedRanges() is called. I will deploy a command to remove orphaned range names from any Google Spreadsheet. It will be part of the NamedRanges routines in dlbTools which is already available in the Google Add-ons Store at https://chrome.google.com/webstore/detail/dlbtools./ochdleihpppeoboanknpbmabbdjfihjj.
I wrote a Google Spreadsheet that demonstrates it, producing the message: - "We're sorry, a server error occurred. Please wait a bit and try again. (line 8, file "Code")". This spreadsheet is available publicly at https://docs.google.com/spreadsheets/d/1IKwGb9guw5Ud1q2YJj3Ao1yjjIvMGv35Pcpe_lyq48I/edit?usp=sharing
The error occurs:
- When method SpreadsheetApp.getNamedRanges() is called.
- Only after deleting a row that had been referenced in a Named Range using a full-row style reference.
- Only if the Named Range reference is like "1:1" instead of "A1:B1".
- Only after the referenced row has been deleted causing the Named Range to refer to "#REF!".
Steps to demonstrate this unexpected server error by using the Google Spreadsheet are:
- Select menu item Tools > Script editor...
- Switch to tab "Find Orphans Scripts"
- Select menu item Run > findOrphans
- Observe the red bar saying, "We're sorry, a server error occurred. Please wait a bit and try again. (line 8, file "Code")"
- Select menu item View > Logs
- Select menu item View > Execution transcript
Here is the test script that demonstrates the server error: function findOrphans() {
// Helper function logs Named Range names and references.
var logNamedRanges = function (scenario) {
Logger.log('Logging named ranges for scenario: ' + scenario);
Logger.log('In logNamedRanges() before call to getNamedRanges()');
var namedRanges = ss.getNamedRanges();
Logger.log('In logNamedRanges() after call to getNamedRanges()');
var names = namedRanges.map(function (nr){return nr.getName() + ' --> ' + nr.getRange().getA1Notation();});
Logger.log('\n' + names.join('\n'));
}
// Helper function alters Named Ranges to full row references.
// That is, "A1:B1" becomes "1:1".
var changeRangeReferencesToFullRow = function () {
Logger.log('In changeRangeReferencesToFullRow() before call to getNamedRanges()');
var namedRanges = shtTest.getNamedRanges();
Logger.log('In changeRangeReferencesToFullRow() after call to getNamedRanges()');
for (var i = 0; i < namedRanges.length; i++) {
var n = namedRanges[i].getRange().getA1Notation();
var num = n.charAt(1);
var fullRowA1Notation = num + ':' + num;
var fullRowNameRange = shtTest.getRange(fullRowA1Notation);
namedRanges[i].setRange(fullRowNameRange);
}
Logger.log('Successfully changed Named Ranges to full row references');
}
// Helper function creates test sheet by copying sheet Static and
// creates Named Ranges for each of the first two rows.
var setupTestSheet = function () {
if (ss.getSheetByName('Copy of Static') != null) {
// Test sheet already exists, so delete it.
ss.deleteSheet(ss.getSheetByName('Copy of Static'));
Logger.log('Deleted existing copy of test sheet');
}
// Activate sheet Static, duplicate it, and grab reference to it.
shtStatic.activate();
ss.duplicateActiveSheet();
shtTest = ss.getSheetByName('Copy of Static');
// Create or update Named Nanges pointing to the first two rows.
ss.setNamedRange('row1Name', shtTest.getRange('1:1'));
ss.setNamedRange('row2Name', shtTest.getRange('2:2'));
Logger.log('Successfully set up test sheet');
}
// Helper function runs test sequence.
function doTest (whichPass) {
Logger.log('\n\nStarting test using ' + whichPass + '\n\n');
setupTestSheet();
if (whichPass.slice(0,3) == '1:1') {
// next line is only difference between the passes
changeRangeReferencesToFullRow();
}
logNamedRanges('Before row delete')
shtTest.deleteRow(1);
if (whichPass.slice(-5) == 'error') {
// next line stops test before error occurs
Logger.log('Halted test to avoid error');
return;
}
logNamedRanges('After row delete')
Logger.log('Finished test using ' + whichPass );
}
// Create variables that will be available to helper functions.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var shtStatic = ss.getSheetByName('Static');
var shtTest;
// Execute test sequence three times.
doTest('A1:B1 style references');
doTest('1:1 style references with stop before server error');
doTest('1:1 style references without stopping');
}
Test with Sheets API
@Tanaike's suggestion of using the Sheets API helps because it successfully retrieves the list of Named Ranges where the SpreadsheetAPP call fails. Unfortunately, it does not include the ones of most interest—the orphans.
The demonstration Spreadsheet at https://docs.google.com/spreadsheets/d/1IKwGb9guw5Ud1q2YJj3Ao1yjjIvMGv35Pcpe_lyq48I/edit?usp=sharing now includes an additional script file called trySheetsAPI.gs. The comments at the top tell how to identify the additional code that tests the Sheets API. While I appreciate this helpful and interesting suggestion, it still does not allow me to write a general purpose orphan deletion routine.
