确定包含空单元格的列范围内的最后一行-Google表格 [英] Determining the last row in a range of columns that contain empty cells - Google Sheets

问题描述

我在创建函数以查找指定范围内的数据的最后一行时遇到麻烦，在这种情况下，我有在 A:K 列中移动的数据以及在其中使用该数据的函数 L:Z .我想将数据粘贴到 A:K 范围内的第一个空行中.我尝试使用在特定列中查找第一个空行的解决方案:

  var Avals = ss.getRange("A1:A").getValues();var Alast = Avals.filter(String).length; 

但这对我来说不是一个可行的解决方案，因为我们可能连续有 empty (第一个空值)的第一个值，并且我需要将其粘贴到特定范围内.我试过同时使用 getLastRow 和 getDataRange 方法，但它们不满足要求.

解决方案

目标:

您要查找特定范围列的最后一行，其中一些列可能包含空单元格，甚至整列都可能完全为空.

说明:

• 选择所需的列范围以标识内容的最后一行.

• 优势:

  与此不同，该解决方案有效:

  • 即使该范围内的任何列包含空值(或整个列为空).

  • 用于单列或列范围.

  缺点:

  对于许多列，它可能会变慢.尽管 GAS API调用的数量处于最小可能的水平，但是有很多 JavaScript 函数调用正在迭代使用.

  I'm having trouble creating a function to find the last row with data in a specified range, in this case I have data that moves in columns A:K and functions that use that data in L:Z. I want to paste data in the first empty row in the range A:K. I have tried using the solution of finding the first empty row in a specific column:

  var Avals = ss.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length;
  

  but that isn't a viable solution for me since we may have empty first values in a row, and I need it to paste in a specific range. I've tried using both getLastRow and getDataRange methods but they don't satisfy the requirements.

  解决方案

  Goal:

  You want to find the last row of a particular range of columns and some of these columns could contain empty cells or even the full column could be totally empty.

  Explanation:

  • Select the desired range of columns you want to identify the last row with content.

  • forEach column, use map to get the data of this particular column.

  • reverse the data array of the column so you can findtheIndex of the first non-empty cell (starting backwards).

  • Deduct this row from the maximum rows in the sheet, and it will give you the last row with content of each column in the selected range. Push all these values to an array.

  • The Math.max value of this array will be the last row with content of the selected range.

  Code snippet:

  Iterate through the columns A:K, find the last row for every column and then calculate the maximum last row:

  function findLastRow() {
    const ss = SpreadsheetApp.getActive();
    const sh = ss.getSheetByName('Sheet1');
    const data = sh.getRange("A:K").getValues();
    const mR = sh.getMaxRows();
    const indexes = [];
    data[0].forEach((_,ci)=>{
     let col = data.map(d => d[ci]);
     let first_index = col.reverse().findIndex(r=>r!='');
     if(first_index!=-1){
        let max_row = mR - first_index;
        indexes.push(max_row);
     }
    });
    last_row = indexes.length > 0 ? Math.max(...indexes) : 0;
    console.log(last_row);
  }
  

  Then you can start pasting from last_row+1.

  Example Sheet for code snippet:

  Let's say we want to find the last row in the range A:K for which some columns might contain empty values, and there is another column that has the last row with content in the sheet. In the example sheet below, the correct answer should be 27.

  Advantages:

  Unlike this, the solution works:

  • even if any of the columns in the range contains empty values (or the full column is empty).

  • for both single columns or range of columns.

  Disadvantages:

  It could potentially be slow for a large number of columns. Albeit, the number of GAS API calls are at the minimum possible level, there are many JavaScript function calls that are being used iteratively.

  这篇关于确定包含空单元格的列范围内的最后一行-Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！