Google Sheets Apps Script get range of entire sheet even if it has no data-CodePudding

Let's say I define a sheet thissheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name) where name is a string with the name of one of my sheets.

I know if I have data in the sheet, I can use thissheet.getDataRange() to select it all. Or, alternately thissheet.getRange(1,1,thissheet.getLastRow(),thissheet.getLastCol())

But what if I want the whole sheet selected as a range regardless of whether it has data or not?

Or, in my particular instance, I have ran thissheet.clear() and cleared all the data in my sheet but I need to use thissheet.somerange.removeCheckboxes() where somerange basically represents the entire sheet as a Range object to clear all the empty checkboxes left behind because clear() doesn't clear the checkboxes.

OK, so first thing is I need to make sure I run thissheet.dataRange().removeCheckboxes() in my script sequence before thissheet.clear(). That works so long as I control everything and follow certain rules. This still leaves two problems:

Once I deploy my script and other users start using it, it would be fairly easy for someone to clear the sheet and leave the checkboxes behind and it will cause my script to crash later because .dataRange() will effectively be no range.
If I have checkboxes in the last row or last column with no other data, they still won't get cleared because they won't be part of .dataRange()

So how can I ensure all the checkboxes in my sheet have been removed?

CodePudding user response：

1. Removing all checkboxes in a sheet.

In your situation, in order to retrieve all cells in a sheet, how about using getMaxRows and getMaxColumns? When this reflects in a script, it becomes as follows.

const sheetName = "Sheet1"; // Please set the sheet name.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());

range.removeCheckboxes().clear(); // Here, the checkboxes and cells are cleared.

If you want to remove only checkboxes, please modify range.removeCheckboxes().clear() to range.removeCheckboxes().

2. Checking checkboxes in a sheet.

In order to confirm whether all checkboxes are removed in a sheet, how about the following sample script?

const sheetName = "Sheet1"; // Please set the sheet name.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());

const checkCheckboxes = range.getDataValidations().flatMap((r, i) => r.flatMap((c, j) => c && c.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX ? {row: i   1, col: j   1} : []));
if (checkCheckboxes.length == 0) {

  console.log("No checkboxes in this sheet.");

} else {

  console.log("Checkboxes are existing in the cells.");
  console.log(checkCheckboxes); // Here, you can see the cell coordinates of the checkboxes.

}

Note:

For example, I think that Sheets API can be also used in your situation. But I guessed that from your question, using Spreadsheet service might be suitable. So, I proposed the above answers.

References:

CodePudding user response：

For all intents and purposes, checkboxes contain data, whether checked or not. This is also mentioned by @Cooper

Once I deploy my script and other users start using it, it would be fairly easy for someone to clear the sheet and leave the checkboxes behind and it will cause my script to crash later because .dataRange() will effectively be no range.

This is not the case as .getDataRange() will default to A1 in case no data is found.

If I have checkboxes in the last row or last column with no other data, they still won't get cleared because they won't be part of .dataRange()

Checkboxes are part of the datarange. When you add a checkbox, it contains FALSE by default.