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.

我正在处理以下脚本的变体:
如果有特定的"; word在单元格中找到
和
Google表格脚本-在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屋！