Google 表格:删除包含指定数据的行 [英] Google Sheets: delete rows containing specified data
问题描述
我是 Java 脚本和 Google Apps 脚本的新手,所以如果已经回答了这个问题,我很抱歉.在这个项目的最后几个月里,我找不到我想要的东西.
I'm new to Java scripting and Google Apps Scripts so i am sorry if this has already been answered. I was not able to find what i was looking for over the last few months of working on this project.
我正在这里处理脚本的变体:
如果确定删除Google表格中的行词"在单元格中找到
和
Google Sheet Script - 在 Col 中查找值并删除行
I am working on a variant of the scripts here:
Delete row in Google Sheets if certain "word" is found in cell
AND
Google Sheet Script - Find Value in Col and Delete Row
我想创建一个按钮或菜单,允许某人输入特定数据,并删除电子表格中包含该数据的每一行.
I want to create a button, or menu, that will allow someone to enter specific data, and have each row in the spreadsheet containing that data deleted.
我在这里有一个测试表,它说明了我正在处理的数据、我正在使用的公式,并附有脚本的开头:https://docs.google.com/spreadsheets/d/1e2ILQYf8MJD3mrmUeFQyET6lOLYEb-4coDTd52QBWtU/edit?usp=sharing
I have a test sheet here that illustrates the data i'm working with, formulas i'm using, and has the beginning of the script attached to it: https://docs.google.com/spreadsheets/d/1e2ILQYf8MJD3mrmUeFQyET6lOLYEb-4coDTd52QBWtU/edit?usp=sharing
前 4 张表格通过每张表格中单元格 A:3 中的公式从表格回复 1"表格中提取数据,因此只需从表格回复 1"表格中删除数据即可将其清除其余的床单.
The first 4 sheets are pulling data from the "Form Responses 1" sheet via a formula in cell A:3 in each sheet so the data would only need to be deleted from the "Form Responses 1" sheet to clear it from the rest of the sheets.
我尝试过这样做,但我认为我没有走在正确的轨道上.https://developers.google.com/apps-script/guides/dialogs
I tried working this in but i do not think i am on the right track. https://developers.google.com/apps-script/guides/dialogs
我也在 60 天前在 Google 文档帮助论坛上发布了此内容,但没有收到任何回复.任何帮助将不胜感激.
I also posted this on Google Docs Help Forum 60 days ago, but have not received any responses. Any help would be greatly appreciated.
推荐答案
有几个步骤.为了 UI 的可用性,这需要更长的代码.简明形式:
There's a few steps. For usability of UI this takes a little longer code. In concise form:
- 用户激活一个对话框并输入一个字符串.
- 删除带有字符串的行(带有错误处理和确认)
(希望这可以帮助您入门,并且您可以根据自己的需要进行定制)
(Hopefully this gets you started and you can tailor it to your needs)
启动菜单的函数:
function onOpen(){
SpreadsheetApp.getUi()
.createMenu('My Menu')
.addItem('Delete Data', 'deleteFunction')
.addToUi();
}
主力:
function deleteFunction(){
//declarations
var sheetName = "Form Responses 1";
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName(sheetName);
var dataRange = sheet.getDataRange();
var numRows = dataRange.getNumRows();
var values = dataRange.getValues();
var delete_string = getUIstring();//open initial UI, save value
if (delete_string.length < 3) return shortStringError()//UI to protect your document from an accidental entry of a very short string.
//removing the rows (start with i=2, so don't delete header row.)
var rowsDeleted = 0;
for (var i = 2; i <= numRows; i++){
var rowValues = values[i-1].toString();//your sheet has various data types, script can be improved here to allow deleting dates, ect.
if (rowValues.indexOf(delete_string) > -1){
sheet.deleteRow(i - rowsDeleted);//keeps loop and sheet in sync
rowsDeleted++;
}
}
postUIconfirm(rowsDeleted);//Open confirmation UI
}
隔离的 UI 功能,有助于使上述功能更加简洁:
Isolated UI functions to help make above function more concise:
function getUIstring(){
var ui = SpreadsheetApp.getUi();
var response = ui.prompt("Enter the target data element for deletion")
return response.getResponseText()
}
function postUIconfirm(rowsDeleted){
var ui = SpreadsheetApp.getUi();
ui.alert("Operation complete. There were "+rowsDeleted+" rows deleted.")
}
function shortStringError(){
var ui = SpreadsheetApp.getUi();
ui.alert("The string is too short. Enter a longer string to prevent unexpected deletion")
}
这篇关于Google 表格:删除包含指定数据的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!