Deletes a range of cells and shifts other cells into the deleted area. There are several related tasks that are implemented by other functions:
To clear cells of their value and/or format, use
range_clear[]
.To delete an entire [work]sheet, use
sheet_delete[]
.To change the dimensions of a [work]sheet, use
sheet_resize[]
.
Usage
range_delete[ss, sheet = NULL, range, shift = NULL]
Arguments
ssSomething that identifies a Google Sheet:
its file id as a string or
drive_id
a URL from which we can recover the id
a one-row
dribble
, which is how googledrive represents Drive filesan instance of
googlesheets4_spreadsheet
, which is whatgs4_get[]
returns
Processed through as_sheets_id[]
.
Sheet to delete, in the sense of "worksheet" or "tab". You can identify a sheet by name, with a string, or by position, with a number. Ignored if the sheet is specified via range
. If neither argument specifies the sheet, defaults to the first visible sheet.
Cells
to delete. There are a couple differences between range
here and how it works in other functions [e.g. range_read[]
]:
range
must be specified.range
must not be a named range.range
must not be the name of a [work] sheet. Instead, usesheet_delete[]
to delete an entire sheet. Row-only and column-only ranges are especially relevant, such as "2:6" or "D". Remember you can also use the helpers incell-specification
, such ascell_cols[4:6]
, orcell_rows[5]
.
Must be one of "up" or "left", if specified. Required if range
is NOT a rows-only or column-only range [in which case, we can figure it out for you]. Determines whether the deleted area is filled by shifting surrounding cells up or to the left.
Value
The input ss
, as an instance of sheets_id
See also
Examples
# create a data frame to use as initial data
df ✔ Editing range-delete-example.
#> ✔ Deleting cells in sheet Sheet1.
# delete a column
range_delete[ss, range = "C"]
#> ✔ Editing range-delete-example.
#> ✔ Deleting cells in sheet Sheet1.
# delete a rectangle and specify how to shift remaining cells
range_delete[ss, range = "B3:F4", shift = "left"]
#> ✔ Editing range-delete-example.
#> ✔ Deleting cells in sheet Sheet1.
# clean up
gs4_find["range-delete-example"] %>%
googledrive::drive_trash[]
#> File trashed:
#> • range-delete-example
Hiding and Deleting Rows and Columns on Google Spreadsheet using Google Apps Script
These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this. In this case, when the process costs of
the scripts created by using Spreadsheet service and Sheets API are compared, the cost of script created by Sheets API is much lower than that of script created by Spreadsheet service. So when the rows and columns of your Spreadsheet is large and you can use Sheets API, I recommend to use Sheets API. As the sample situation, the following sample Spreadsheet is used. The 1st row and the column "A" are used as the header row and header column, respectively. Each header has the checkboxes. This sample Spreadsheet is used for the following scripts. The rows of checked checkboxes of the column "A" are hidden. In this sample script, Sample situation
Hide rows
Sample script 1
hideRow
of Spreadsheet service is used.// Hidden rows using Spreadsheet service.
function hideRows_with_SpreadsheetService[] {
const checkBoxColumn = 1;
const sheetName = "Sheet1";
const sheet = SpreadsheetApp.getActiveSpreadsheet[].getSheetByName[sheetName];
for [let r = 2; r {
if [a === true] {
const offset = i + checkBoxColumn;
ar.push[{
updateDimensionProperties: {
range: {
sheetId: sheetId,
startIndex: offset,
endIndex: offset + 1,
dimension: "ROWS",
},
properties: { hiddenByUser: true },
fields: "hiddenByUser",
},
}];
}
return ar;
}, []];
Sheets.Spreadsheets.batchUpdate[{ requests: requests }, spreadsheetId];
}
Hide columns
The columns of checked checkboxes of the 1st row are hidden.
Sample script 1
In this sample script, hideColumn
of Spreadsheet service is used.
// Hidden columns using Spreadsheet service. function hideColumns_with_SpreadsheetService[] { const checkBoxRow = 1; const sheetName = "Sheet1"; const sheet = SpreadsheetApp.getActiveSpreadsheet[].getSheetByName[sheetName]; for [let c = 2; c { if [e === true] { const offset = i + checkBoxRow; ar.push[{ updateDimensionProperties: { range: { sheetId: sheetId, startIndex: offset, endIndex: offset + 1, dimension: "COLUMNS", }, properties: { hiddenByUser: true }, fields: "hiddenByUser", }, }]; } return ar; }, []]; Sheets.Spreadsheets.batchUpdate[{ requests: requests }, spreadsheetId]; }
Delete rows
The rows of checked checkboxes of the column "A" are deleted. As an important point, when the rows are deleted, the row index is changed. By this, when the rows are deleted from the bottom row, the script becomes simpler.
Sample script 1
In this sample script, deleteRow
of Spreadsheet service is used.
// Delete rows using Spreadsheet service. function deleteRows_with_SpreadsheetService[] { const checkBoxColumn = 1; const sheetName = "Sheet1"; const sheet = SpreadsheetApp.getActiveSpreadsheet[].getSheetByName[sheetName]; for [let r = sheet.getLastRow[]; r >= 2; r--] { const range = sheet.getRange[r, checkBoxColumn]; if [range.isChecked[]] sheet.deleteRow[r]; } }
Sample script 2
In this sample script, deleteDimension
of the batchUpdate method in Sheets API is used. Before you use this script,
please enable Sheets API at Advanced Google services.
// Delete rows using Sheets API. function deleteRows_with_SheetsAPI[] { const checkBoxColumn = 1; const sheetName = "Sheet1"; const ss = SpreadsheetApp.getActiveSpreadsheet[]; const sheet = ss.getSheetByName[sheetName]; const spreadsheetId = ss.getId[]; const sheetId = sheet.getSheetId[]; const values = sheet .getRange[2, checkBoxColumn, sheet.getLastRow[] - 1] .getValues[]; const requests = values .reduce[[ar, [a], i] => { if [a === true] { const offset = i + checkBoxColumn; ar.push[{ deleteDimension: { range: { sheetId: sheetId, startIndex: offset, endIndex: offset + 1, dimension: "ROWS", }, }, }]; } return ar; }, []] .reverse[]; Sheets.Spreadsheets.batchUpdate[{ requests: requests }, spreadsheetId]; }
Delete columns
The columns of checked checkboxes of the 1st row are deleted. As an important point, when the columns are deleted, the column index is changed. By this, when the columns are deleted from the end of column, the script becomes simpler.
Sample script 1
In this sample script, deleteColumn
of Spreadsheet service is used.
// Delete columns using Spreadsheet service. function deleteColumns_with_SpreadsheetService[] { const checkBoxRow = 1; const sheetName = "Sheet1"; const sheet = SpreadsheetApp.getActiveSpreadsheet[].getSheetByName[sheetName]; for [let c = sheet.getLastColumn[]; c >= 2; c--] { const range = sheet.getRange[checkBoxRow, c]; if [range.isChecked[]] sheet.deleteColumn[c]; } }
Sample script 2
In this sample script, deleteDimension
of the batchUpdate method in Sheets API is used. Before you use this script, please enable Sheets API at Advanced Google
services.
// Delete columns using Sheets API. function deleteColumns_with_SheetsAPI[] { const checkBoxRow = 1; const sheetName = "Sheet1"; const ss = SpreadsheetApp.getActiveSpreadsheet[]; const sheet = ss.getSheetByName[sheetName]; const spreadsheetId = ss.getId[]; const sheetId = sheet.getSheetId[]; const values = sheet .getRange[checkBoxRow, 2, 1, sheet.getLastColumn[] - 1] .getValues[][0]; const requests = values .reduce[[ar, e, i] => { if [e === true] { const offset = i + checkBoxRow; ar.push[{ deleteDimension: { range: { sheetId: sheetId, startIndex: offset, endIndex: offset + 1, dimension: "COLUMNS", }, }, }]; } return ar; }, []] .reverse[]; Sheets.Spreadsheets.batchUpdate[{ requests: requests }, spreadsheetId]; }
Note
When you want to use above scripts using OnEdit event trigger, please be careful as follows.
The script created by using Spreadsheet service can work using the simple trigger like below.
const onEdit = [] => deleteRow_with_SpreadsheetService[];
But, the script created by using Sheets API can NOT work using the simple trigger. In this case, please use the OnEdit event trigger of the installable trigger. Please be careful this.
References
- Advanced Google services
- hideRow[row]
- hideColumn[column]
- UpdateDimensionPropertiesRequest
- deleteRow[rowPosition]
- DeleteDimensionRequest
- deleteColumn[columnPosition]
- Simple Triggers
- Installable Triggers