天道酬勤,学无止境

How to make my 'Show and hide rows' script function properly in Google Sheets

I am trying to have a script in (google sheets) sheet "Info Pull" show/hide rows based on the contents of Column C.

Currently I am running the following formula in Column C:

=if(or(len(H47)>0,len(I47)>0,len(J47)>0,len(K47)>0),"unhide","hide")

which produces either 'hide' or 'unhide' depending on whether or not cells H,I,J,and K 47 are returning a value.

If any of the aforementioned cells return a value, the corresponding cell in Column C turns to 'unhide' and vice-versa if all the cells return no value.

I've been trying to have this script hide/unhide the rows based on the contents of Column C however, the script is only partially functioning.

If I actively type 'hide' into a C cell, the row will hide. However, if I paste 10 rows worth of 'hide' into 10 C cells, only the first row will hide. Likewise, the script is not re-evaluating and hiding/unhiding based off of the changing results of the IF formula. If I depopulate cell H47, turning C47 to hide, the cell does not hide.

Also, if I set cell A1 to 'hide' and paste '=$A$1' in a group of cells in Column C, only the first cell in the group will hide and it will not unhide if I set A1 to 'unhide'. I think this may have something to do with my trigger but I am relatively new to scripting and am unsure.

function onEdit(e) {
  hideAndShowRows(e);
}

    function hideAndShowRows(e) {

  var sheetsToWatch = ['Info Pull'];
  var columnToWatch = 3;

  var sheet = e.range.getSheet();
  if (sheetsToWatch.indexOf(sheet.getName()) < 0)  {
    return;
  }
  var editedRow = e.range.getRow();
  var cellToWatch = sheet.getRange(editedRow, columnToWatch);
  if (cellToWatch.getValue().toLowerCase() === 'hide') {
    sheet.hideRows(editedRow);
  }
  if (cellToWatch.getValue().toLowerCase() === 'unhide') {
    sheet.showRows(editedRow);
  }
}

I would like the cells to hide/unhide based off of whether or not the formula in Column C returns hide/unhide.

评论

Your code is "failing" because it only gets the row number of the top-left cell of the edited range

var editedRow = e.range.getRow();

and

var cellToWatch = sheet.getRange(editedRow, columnToWatch);

You could use the following properties to get the range dimensions

e.range.rowStart
e.range.columnStart
e.range.rowEnd
e.range.columnEnd

or to get the reference of the edited range use

e.range.getA1Notation();

Related

  • Hide Rows Based on Content of a Cell -issue with onEdit-
  • Where can we find non-documented attributes of Google Objects
  • Hide Rows when a criteria is met

Here is a modified function that will solve few issues:

function onEdit(e) {
  hideAndShowRows(e);
}

    function hideAndShowRows(e) {

  var sheetsToWatch = ['Setup'];
  var columnToWatch = 3;

  var sheet = e.range.getSheet();
  if (sheetsToWatch.indexOf(sheet.getName()) < 0)  {
    return;
  }
  var editedRow = e.range.getRow();
  var numOfRows = e.range.getNumRows()
  for (var i = 0; i<numOfRows ; i++){
  var cellToWatch = sheet.getRange(editedRow + i, columnToWatch);
  if (cellToWatch.getValue().toLowerCase() === 'hide') {
    sheet.hideRows(editedRow + i);
  }
  if (cellToWatch.getValue().toLowerCase() === 'unhide') {
    sheet.showRows(editedRow + i);
  }
  }
}

Firstly, your code only works on the row that was edited, it doesn't look for any other row for changes. Specifically this line:

var editedRow = e.range.getRow()

e is a event object passed to simple triggers, which contain info like which range was edited was in this case. The above function getRow() gets the first row in the range which was edited, hence when you edit multiply rows only the first one gets hidden. To get all the rows that were edited you will have use getNumRows() and loop through each one of them like so:

var editedRow = e.range.getRow();
  var numOfRows = e.range.getNumRows()
  for (var i = 0; i<numOfRows ; i++){
  var cellToWatch = sheet.getRange(editedRow + i, columnToWatch);
  if (cellToWatch.getValue().toLowerCase() === 'hide') {
    sheet.hideRows(editedRow + i);
  }
  if (cellToWatch.getValue().toLowerCase() === 'unhide') {
    sheet.showRows(editedRow + i);
  }
  }

Finally, a caveat when you point all the cells to A1 and only edit row A (just cell A1), the program will only look for changes in that row i.e row A. You can find more resource here.

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • 用于批量隐藏行的 Google 表格脚本(Google sheets Script to Hide Rows in Batch)
    问题 我目前正在使用此脚本来隐藏 col K 上包含 0 的行 function Hide() { var s = SpreadsheetApp.getActive() .getSheetByName('Sheet1'); s.getRange('K:K') .getValues() .forEach(function (r, i) { if (r[0] !== '' && r[0].toString() .charAt(0) == 0) s.hideRows(i + 1) }); } 哪个工作得很好,唯一的问题是当我运行脚本时,它会逐行隐藏(现在我有很多行需要很多时间)。 有没有办法将其更改为批量工作? 回答1 这是创造魔法的脚本 function Hide() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Ventas"); var currentRange = ss.getRangeByName("RangeCalculation"); var rangeStart = currentRange.getRow(); var values = currentRange.getValues(); var index = 0, rows = 1; var show =
  • Google 表格:根据第 1 行中的日期隐藏列(Google Sheets: Hiding Columns based on date in row 1)
    问题 我没有在 Excel 或 Google Sheets 中编写脚本的经验,所以我试图扩展一下,看看是否有解决我的问题的方法。 我们在厨房改造业务中使用 Google 表格作为每周日历。 我们从左到右组织几周,并在这些列中列出我们目前正在处理的工作。 我想自动隐藏日期超过 4 周的所有列,因此当工作表打开时,我们不会从一年前的日期开始。 我可以每周手动隐藏这些列,但是当我确实需要返回查看前几周时,我不得不取消隐藏所有这些列,然后突出显示我想要重新隐藏的所有列。 拥有脚本似乎是更好的解决方案。 有没有办法在每次打开文件时运行脚本,以便我们始终只显示前 4 周和未来的所有内容? 如果是这样,您是否愿意帮助我了解如何编写并使其工作? 再说一次,当涉及到公式以外的任何事情时,我是一个新手,但对了解有关脚本功能的更多信息非常感兴趣。 谢谢! 回答1 通过Tools->Script Editor使用 Apps 脚本,您可以创建一个带有 onOpen() 函数的菜单。 菜单中的函数(例如hidePast )然后需要检查每列中的给定值(以查看该列所指的日期),然后将其标记为隐藏与否。 onOpen函数,因为它是一个“简单的触发器”,不能做任何需要“授权”的事情(比如与非本地电子表格数据交互),因此是中间方法。 通过创建菜单,您可以让任何使用电子表格的人轻松授权和激活该功能。 例子: /*
  • 有没有办法将电子表格的多个选项卡(但不是全部)打印为单个 pdf?(Is there a way to print multiple tabs of a spreadsheet (but not all of them) as a single pdf?)
    问题 我已经做了一些研究,但还没有找到任何方法来做到这一点。 试图按照 Andrew Roberts 在这里解释的方法 http://www.andrewroberts.net/2017/03/apps-script-create-pdf-multi-sheet-google-sheet/ 将电子表格(或特定标签)转换为 pdf : function convertSpreadsheetToPdf(email, spreadsheetId, sheetName, pdfName) { var spreadsheet = spreadsheetId ? SpreadsheetApp.openById(spreadsheetId) : SpreadsheetApp.getActiveSpreadsheet(); spreadsheetId = spreadsheetId ? spreadsheetId : spreadsheet.getId() var sheetId = sheetName ? spreadsheet.getSheetByName(sheetName).getSheetId() : null; var pdfName = pdfName ? pdfName : spreadsheet.getName(); var parents = DriveApp
  • 如何在 Google Sheets 中增强我的自动隐藏脚本,使其不会超时(How can I enhance my auto hide script in Google Sheets so it wont time out)
    问题 我有一个在多个电子表格上运行的脚本...它会自动隐藏包含特定值的行。 目前,该脚本设置为每天凌晨 3:00 左右运行,以确保在处理过程中没有人处于活动状态。 问题是我现在遇到的是这些工作表变得太大而无法使用我当前逐行运行的脚本。 脚本超时且未完成。 我猜它仍然在所有已经隐藏的行上运行脚本。 这是我当前的脚本,这是非常基本的: function autoHide() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("SHIPPING"); //get data from column var data = sheet.getRange('AD:AD').getValues(); //iterate over all rows for(var i=0; i< data.length; i++){ //compare first character, if greater than 0, then hide row if(data[i][0] > 0){ sheet.hideRows(i+1); } } } 我尝试寻找更好的选择,并发现人们在谈论使用数组过滤器或批量运行的地方,只是不同的事情似乎没有得到足够的解释,无法将其转化为我正在研究的内容。
  • 如何在一个 Google 表格电子表格中的多个范围的列上运行我的隐藏/显示列脚本?(How can I run my hide/show columns script on multiple ranges of columns in one google sheets spreadsheet?)
    问题 我没有用 javascript 编写程序,但我设法在网上找到了下面的脚本,它允许我隐藏一个范围的列。 问题是我需要能够对一张纸中的多个列范围重复此操作。 所以我可以用一个按钮隐藏/显示列 C-AG 并用另一个按钮单独隐藏/显示列 AI - BJ(并且这样做多月)。 这是用于具有大量数据的约会,因此我可以轻松隐藏和显示每个月的约会。 请参阅下面的图片,这演示了该功能。 (第一张图显示一月显示,第二张图显示一月隐藏。) 运行隐藏脚本之前的电子表格 - 运行隐藏脚本后的电子表格 另请参阅此脚本放在我的工具栏中的自定义菜单的图像。 在这个菜单中有两个按钮,第一个是显示 C-AG,第二个是隐藏 C-AG。 由脚本创建的自定义菜单 这是允许我隐藏/显示一系列列 (C-AG) 并且运行良好但仅限于一组列(一个月)的脚本: function onOpen() { var menu = [{name: "Show C-AG", functionName: "showColumns"}, {name: "Hide C- AG", functionName: "hideColumns"}] SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu); } function showColumns() { var ss =
  • 是否可以一次隐藏 Google 电子表格的多行?(Is it possible to hide many rows of a Google spreadsheet at once?)
    问题 所以我在下面使用这个脚本(来源:Eduardo Kuwakino)。 我遇到的问题是它从 A:A 列中一一隐藏了值为“x”的行。 是否有可能让编辑这些电子表格的人“不那么痛苦”? function hideCells(sheet) { var maxRows = sheet.getMaxRows(); //show all the rows sheet.showRows(1, maxRows); //get data from clumn A var data = sheet.getRange('A:A').getValues(); //iterate over all rows for(var i=0; i< data.length; i++){ //compare first character, if asterisk, then hide row if(data[i][0].charAt(0) == 'x'){ sheet.hideRows(i+1); } }} function onOpen() { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); for(var i=0; i< sheets.length; i++){ hideCells(sheets[i]); } } 回答1
  • Google 脚本根据单元格文本隐藏行(Google script hide rows based on cell text)
    问题 我目前正在尝试找到一种方法来隐藏第 11 到 16 页的 B 列中包含文本“是”的每一行。目前我可以让它隐藏包含“是”的行,但仅在第 11 页上而不是在接下来的几个。 任何有关如何解决此问题的想法将不胜感激。 function Hide() { var ss = SpreadsheetApp.getActiveSpreadsheet(); for (var i = 11; i <= 16; i++) { var sheet = ss.getSheets()[i]; sheet.showRows(1, sheet.getMaxRows()); for (var i = 1; i < sheet.getMaxRows()+1; ++i) { if (sheet.getRange(i,2).getValue() == "Yes") { sheet.hideRows(i,1); } } } } 回答1 您使用i两次,一次用于工作表,一次用于行。 此外,如果您有很多行,那么读取该列一次然后遍历数据是有意义的。 function Hide() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); for (var sheetI = 1; sheetI <= 3; sheetI++) {
  • How can I run my hide/show columns script on multiple ranges of columns in one google sheets spreadsheet?
    I do not code program in javascript but I managed to find the script below online which allows me to hide one range of columns. Problem is I need to be able to repeat this for multiple ranges of columns in one sheet. so I can hide/show columns C-AG with one button and separately hide/show columns AI - BJ with another button (and do this for multiple months). This is for appointments with lots of data so I can easily hide and show each month of appointments. Please see below images this demonstrates the functionality. (First image shows January shown, second shows January hidden.) Spreadsheet
  • How can I enhance my auto hide script in Google Sheets so it wont time out
    I have a script that I run on multiple Spreadsheets... it auto hides rows that contain a certain value. Currently this script it setup to run daily around 3:00 am, to ensure no one is active in it while it processes. The issue is I am now running into is these sheets are getting too large to use my current script, which runs line by line. The script times out and doesn't finish. I'm guessing it still runs the script on all the lines that are already hidden. Here is my current script, which is pretty basic: function autoHide() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss
  • Is it possible to hide many rows of a Google spreadsheet at once?
    So I'm using this script below (credit: Eduardo Kuwakino). The problem I have that it hides the rows from column A:A with the value "x" one by one. Is it possible to make this "less painful" for the people who are editing these spreadsheets? function hideCells(sheet) { var maxRows = sheet.getMaxRows(); //show all the rows sheet.showRows(1, maxRows); //get data from clumn A var data = sheet.getRange('A:A').getValues(); //iterate over all rows for(var i=0; i< data.length; i++){ //compare first character, if asterisk, then hide row if(data[i][0].charAt(0) == 'x'){ sheet.hideRows(i+1); } }}
  • 如何在多个工作表上运行脚本,Google Sheets(How to run a script on multiple sheets, Google Sheets)
    问题 我有一个脚本,我想在我的 Google 工作表中的特定选项卡上运行,但不一定是所有选项卡。 我尝试做两个不同命名的脚本,但只有最后一个保存的脚本会运行。 如何编写此脚本以在特定选项卡上运行? 这是我的开始脚本: function onEdit() { var s = SpreadsheetApp.getActive().getSheetByName('Proposal'); s.showRows(1, s.getMaxRows()); s.getRange('B:B') .getValues() .forEach( function (r, i) { if (r[0] == 'Hide') s.hideRows(i + 1); }); } 我尝试使用来自发布的另一个问题的建议进行这样的修改,但没有奏效。 function onEdit() { var tabs = [ 'Proposal', 'Materials List - All', 'Materials List - Shingles', 'Materials List - Access', 'Work Order' ]; var ss=SpreadsheetApp.getActiveSpreadsheet(); for (var i = 0; i < tabs.length; i++) { var sheet =
  • 使用Google Apps脚本将所有工作表转换为PDF(Convert all sheets to PDF with Google Apps Script)
    问题 我正在尝试将具有多个工作表的Google电子表格转换为PDF文件。 下面的脚本可以工作,但是只能使用电子表格的最后一页创建PDF。 function savePDFs() { SpreadsheetApp.flush(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); var url = ss.getUrl(); //remove the trailing 'edit' from the url url = url.replace(/edit$/,''); //additional parameters for exporting the sheet as a pdf var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf //below parameters are optional... '&size=letter' + //paper size '&portrait=false' + //orientation, false for landscape '&fitw=true' + //fit to width, false for actual size '&sheetnames
  • “GAPI 未定义”消息("GAPI is not defined" message)
    问题 我正在尝试将 Google Sheets API 包含在我的网络应用程序中,但我一直收到一个错误,指出未定义 gapi 库。 我尝试使用 ComponentDidMount 生命周期方法延迟对服务器的请求,甚至在该方法中使用超时,但我一直收到相同的错误。 如何在我的应用程序中定义要使用的 gapi 库? import React from 'react'; var CLIENT_ID = ''; var SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]; export default class MyNavbar extends React.Component { constructor(props) { super(props); } componentDidMount(){ this.checkAuth(); } /** * Check if current user has authorized this application. */ checkAuth(){ gapi.auth.authorize( { 'client_id': CLIENT_ID, 'scope': SCOPES.join(' '), 'immediate': true }, this
  • 谷歌表到pdf mailto(google sheet to pdf mailto)
    问题 到目前为止,我更改了编码,现在可以将电子表格发送到电子邮件地址。 我现在正在寻找的是选择特定范围(如 (A1:J23))并仅将该范围发送到我的邮件地址的可能性。 有什么想法我在这里做错了吗? 总是收到警告:类型错误:在对象范围中找不到函数 getSheets。 (第 8 行,文件 function onOpen() { var submenu = [{name:"einreichen", functionName:"sendEmailWithPdfAttach"}]; SpreadsheetApp.getActiveSpreadsheet().addMenu('E-mail senden', submenu); } var range = 'A1:J23' var source = SpreadsheetApp.getActiveSpreadsheet().getRange(range); var subject = source.getSheets()[0].getRange('B3').getValue(); var body = source.getSheets()[0].getRange('F3').getValue(); var sheetNum = 0; // first sheet(tab) is zero, second sheet is 1, etc
  • 如何在 php 中隐藏/保护密码详细信息?(How to hide/protect password details in php?)
    问题 我正在制作一个网站,我试图在其中创建一个表单,该表单将用户输入发送到我的谷歌文档/驱动器中的谷歌电子表格...它包括脚本所需的 2 个其他 php 文件。 代码如下: 我的问题是,如何在 $u = / $p = 下的脚本中隐藏我的密码? 查看代码的任何人都可以看到我的密码..我该如何防止? 脚本源的链接是:http://www.farinspace.com/saving-form-data-to-google-spreadsheets/ <?php // Zend library include path set_include_path(get_include_path() . PATH_SEPARATOR . "$_SERVER[DOCUMENT_ROOT]/ZendGdata-1.8.1/library"); include_once("Google_Spreadsheet.php"); $u = "username@gmail.com"; $p = "password"; $ss = new Google_Spreadsheet($u,$p); $ss->useSpreadsheet("My Spreadsheet"); $ss->useWorksheet("wks2"); // important: // adding a leading alpha char
  • How to run a script on multiple sheets, Google Sheets
    I have a script that I would like to run on specific tabs in my Google worksheet, but not necessarily all of the tabs. I tried doing two differently named scripts, but only the last one that was saved will run. How can I code this script to run on specific tabs? This is my beginning script: function onEdit() { var s = SpreadsheetApp.getActive().getSheetByName('Proposal'); s.showRows(1, s.getMaxRows()); s.getRange('B:B') .getValues() .forEach( function (r, i) { if (r[0] == 'Hide') s.hideRows(i + 1); }); } I tried modifying like this, using suggestions from another question posted, but it did
  • 如果 A 列为空,则隐藏 google 电子表格中的行?(Hide rows in google spreadsheet if Column A is empty?)
    问题 如果 A 列为空,我试图隐藏行。 我想将此应用于特定工作表(第 1 周、第 2 周、第 3 周、第 4 周和第 5 周)。 这是我目前为 Week1 编写的代码。 function ConditionalHideRow() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Week1"); var condition = sheet.getRange("A:A").getValue(); if (condition = "EMPTY") { sheet.hideRows(1,125) } 编辑:额外的问题,当 A 列被填满时,有没有办法让它取消隐藏? 我正在使用 =query 公式来填充我的电子表格,随着添加更多数据,长度将需要更改。 回答1 我假设 'EMPTY' 并不是真正要在 col A 中找到的字符串,您确实想检查 col A 是否真的为空? 如果是这样,请尝试: function hideRows() { ["Week1", "Week2", "Week3", "Week4", "Week5"].forEach(function (s) { var sheet = SpreadsheetApp.getActive() .getSheetByName(s)
  • 如果单元格包含“否”,则在Google表格中隐藏行-多个表格(Hide Row in Google Sheets if Cell Contains “no” - Multiple Sheets)
    问题 我正在运行1个主电子表格,其中有4个特定于产品的电子表格从中获取信息。 如果未提取任何相关信息,则较小的电子表格将在所有单元格中显示“否”。 我希望隐藏带有“否”的行。 我有点把东西弄混了,那种作品。 但是我无法在所有4张纸上都使用它。 我试过了getSheets(),我试过了getSheetByName(name)并命名所有4张纸...这是行不通的。 这是我到目前为止的内容: function onOpen() { var ss = SpreadsheetApp.getActive(); var sheets = ss.getSheets(); // array of all sheet objects in ss var numSheets = ss.getNumSheets(); // count of sheets //show all the rows sheet.showRows(1, maxRows); //get data from clumn C var data = sheet.getRange('C:C').getValues(); //iterate over all rows for(var i=0; i< data.length; i++){ //compare column, if no, then hide row if(data[i][0]
  • 以 PDF 格式通过电子邮件发送 Google 表格(Emailing Google Sheet as PDF)
    问题 我有这个脚本,它通过电子邮件向我发送了 Google 电子表格的 PDF。 我只希望它通过电子邮件向我发送第一个“标签”,如果可能的话,作为单个 PDF 副 zip 文件。 想知道是否有人可以提供帮助。 还有一个“标签”是隐藏的,所以我不知道这是否有影响。 他是样品表的链接。 代码如下所示: /* Send Spreadsheet in an email as PDF, automatically */ function emailSpreadsheetAsPDF() { // Send the PDF of the spreadsheet to this email address var email = "xxxxx@gmail.com"; // Get the currently active spreadsheet URL (link) // Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>"); var ss = SpreadsheetApp.getActiveSpreadsheet(); // Subject of email message var subject = "Sample Sheet " + ss.getName(); // Email Body can be HTML too with
  • Google Sheets: Hiding Columns based on date in row 1
    I have no experience with scripting in Excel or Google Sheets, so I'm trying to branch out a bit and see if there's a solution to my problem. We use Google Sheets for a weekly calendar at our kitchen remodeling business. We organize the weeks from left to right and list the jobs we're currently working on in those columns. I would like to automatically hide all columns that have a date older than 4 weeks, so when the sheet opens, we're not starting with a date from a year ago. I can hide these columns manually each week, but when I do need to go back and look at previous weeks, I'm forced to