如何忽略 getRange().getValues() 的空单元格值 [英] How to ignore empty cell values for getRange().getValues()

问题描述

我可以使用 getValues() 获取范围值，并通过在 Google App Script 中声明以下变量将其放入字符串

I am able to get the range values using getValues() and put it into a string by declaring the following variables in Google App Script

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getSheetByName("Test"); 
var range_input = ss.getRange("A1:A").getValues();

但是，我意识到我的字符串中有很多逗号可能来自所有空调用.

However, I realize I am getting a lot of commas in my string probably from all the empty calls.

例如，如果值如下

================
Spreadsheet("Test") Values

A1=abc
A2=def
A3=    
A4=
A5=
A6=uvw
A7=xyz
================

如果我执行 msgBox，它会得到如下所示的内容.

If I do msgBox, it gets something like below.

Browser.msgBox(range_input) // results = abc,def,,,,uvw,xyz,,,,,,,,,,,

有没有办法删除尾随的逗号，以便我得到如下内容?(即忽略空单元格)

Is there a way to remove the trailing commas so I get something like below? (i.e. ignore the empty cells)

Browser.msgBox(range_input) // results = abc,def,uvw,xyz

推荐答案

• 您希望达到以下结果.

  • You want to achieve the following result.

    • 输入

    A1=abc
    A2=def
    A3=
    A4=
    A5=
    A6=uvw
    A7=xyz
    

  • 输出

  • Output

    Browser.msgBox(range_input) // results = abc,def,uvw,xyz
    

  现阶段我认为虽然var result = [i for each (i in range_input)if (isNaN(i))]的推导虽然还可以用，但不是适合这种情况作为 tehhowch 的评论.Alto 我认为 filter() 适合这种情况.在此更新中，我想通过提出其他解决方案来更新此更新.如果这有用，我很高兴.

  In the current stage, I thought that although the comprehensions of var result = [i for each (i in range_input)if (isNaN(i))] can be still used, it is not suitable for this situation as tehhowch's comment. Alto I think that filter() is suitable for this situation. In this update, I would like to update this by proposing other solution. If this was useful, I'm glad.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Test"); 
  var range_input = ss.getRange("A1:A").getValues();
  var result = range_input.reduce(function(ar, e) {
    if (e[0]) ar.push(e[0])
    return ar;
  }, []);
  Logger.log(result) // ["abc","def","uvw","xyz"]
  Browser.msgBox(result)
  

  • 在这种模式中，空行被 reduce() 删除.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Test"); 
  var range_input = ss.getRange("A1:A").getValues();
  var result = [].concat.apply([], range_input).filter(String); // or range_input.filter(String).map(String)
  Logger.log(result) // ["abc","def","uvw","xyz"]
  Browser.msgBox(result)
  

  • 在这种模式中，空行被 filter() 删除，当使用 filter() 时，返回二维数组.为了返回一维数组，数组被展平.
  • In this pattern, the empty rows are removed by filter() and when filter() is used, the 2 dimensional array is returned. In order to return 1 dimensional array, the array is flatten.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Test"); 
  var range_input = ss.getRange("A1:A").getValues();
  var criteria = SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build();
  var f = ss.getRange("A1:A").createFilter().setColumnFilterCriteria(1, criteria);
  var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/gviz/tq?tqx=out:csv&gid=" + sheet.getSheetId() + "&access_token=" + ScriptApp.getOAuthToken();
  var res = UrlFetchApp.fetch(url);
  f.remove();
  var result = Utilities.parseCsv(res.getContentText()).map(function(e) {return e[0]});
  Logger.log(result) // ["abc","def","uvw","xyz"]
  Browser.msgBox(result)
  

  • 在此模式中，过滤器移除空行，然后检索过滤后的值.
  • reduce()
  • filter()
  • map()
  • 类过滤器

  这篇关于如何忽略 getRange().getValues() 的空单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！