复制到新的工作表上，乘以自己的时间，然后过滤我的结果 [英] Copy to new sheet multiply itself loads of times, and dosent filter my result

查看：52
本文介绍了复制到新的工作表上，乘以自己的时间，然后过滤我的结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的代码会复制自身，并且在结果表中只有几行，而结果行中却有999行以上.

My code does copy itself, and has over 999 line rows in the result sheet, when it only should be a few lines.

创建过滤器时，选中" 777 "在 F 列中，它仍然会在过滤结果中出现.

When i create a filter, and tick off the "777" in column F, it also still apears in the filtered result.

问:是什么原因导致了这种行为，我该如何解决，使其仅复制一次，并且仅过滤结果?

Q: What is causing this behaviour, and how can i fix so it only copy itself once, and only the filtered results?

以下是更多详细信息:

完整代码:

 var partner_list = SpreadsheetApp.openByUrl(url);
var SS = partner_list.getSheetByName("Sheet 1");
var targetSheet = partner_list.getSheetByName("result");

 if (SS.getFilter() != null && SS.getFilter().toString()=='Filter') {
   console.log("have populated filter..", SS.getFilter());
var Avals_pre_D = SS.getFilter().getRange();
var Avals_pre = SS.getFilter().getRange().getValues();

    if (targetSheet) {
        partner_list.deleteSheet(targetSheet);
    } 
       targetSheet = partner_list.insertSheet();
       targetSheet.setName("result"); 
  

console.log("DATA:",Avals_pre);


Avals_pre_D.copyTo(
    targetSheet.getRange('A2:R'),
    SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
    false);
var SS = SpreadsheetApp.openByUrl(same_url).getSheetByName("result"); // aka same.. but the result sheet.
var Avals_pre = targetSheet.getRange("A2:R").getValues();    
} else {
  console.log("no filter..");
 var Avals_pre = SS.getRange("A2:R").getValues(); 
}

console.log(Avals_pre.length);

当尝试复制 Avals_pre 时，出现以下错误:

when trying to copy Avals_pre i get the following error:

TypeError: Avals_pre.copyTo is not a function

avals_pre 的输出如下:

   DATA: [ 
    [ '','','','','','test','','','','','','','','','','','','','','','','','','','','' ],
    [ 1,2,3,4,5,666,'','','',100,'','','','','','','','','','','','','','','','' ],
    [ 4,23,43,44,5,777,'','','',100,'','','','','','','','','','','','','','','','' ],
    [ 43,65,54,65,65,888,'','','',100,'','','','','','','','','','','','','','','','' ],]

(这包括我不想要的结果)

( this includes the result i dont want)

如果我使用 Avals_pre_D 并将其复制，它的行为就像疯了一样.其中 console.log(Avals_pre.length); 等于999.

If i take Avals_pre_D and copy that, it behaves like crazy. where console.log(Avals_pre.length); equals 999.

结果表的图片:

推荐答案

修改点:

  • 我认为您的问题可能是由于以下原因造成的.

    Modification points:

    • I thought that your issue might be due to as follows.

      • 将 SS.getFilter().getRange()用作范围时，
        • 与 copyTo 一起使用时，将检索过滤后的值.这样， Avals_pre_D.copyTo(targetSheet.getRange('A2:R')，SpreadsheetApp.CopyPasteType.PASTE_NORMAL，false); 可用于具有过滤结果的值.在这种情况下，似乎复制了该范围内的显示值.
        • 与 getValues 一起使用时，将检索所有值.这样，第一个 var Avals_pre = SS.getFilter().getRange().getValues(); 即可处理没有过滤结果的值.
        • When SS.getFilter().getRange() is used as the range,
          • When it is used with copyTo, the filtered values are retrieved. By this, Avals_pre_D.copyTo(targetSheet.getRange('A2:R'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); works for the values with the filtered result. It seems that in this case, the showing values in the range are copied.
          • When it is used with getValues, all values are retrieved. By this, the 1st var Avals_pre = SS.getFilter().getRange().getValues(); works for the values without the filtered result.

          然后，当我看到您的脚本时， var Avals_pre = targetSheet.getRange("A2:R").getValues();.在这种情况下，范围是"A2:R".这样，将通过工作表中的最大行来检索值.我认为这可能是您发布的原因.如果我使用Avals_pre_D并将其复制，它的行为就像疯了一样.其中console.log(Avals_pre.length);等于999..

          And, when I saw your script, var Avals_pre = targetSheet.getRange("A2:R").getValues(); is used. In this case, the range is "A2:R". By this, the values are retrieved by the max row in the sheet. I thought that this might be the reason of your issue of If i take Avals_pre_D and copy that, it behaves like crazy. where console.log(Avals_pre.length); equals 999..

          如果您想直接获取工作表中过滤结果的值，我认为此线程可能有用.

          If you want to directly retrieve the values witht the filtered result on the sheet, I thought that this thread might be useful.

          当以上几点反映到您的脚本中时，它如下所示.

          When above points are reflected to your script, it becomes as follows.

          function myFunction() {
  // var url = "###"; // I'm not sure about your "url" and "same_url". So please set these variables.
  // var same_url = url;

  var partner_list = SpreadsheetApp.openByUrl(url);
  var SS = partner_list.getSheetByName("Sheet 1");
  var targetSheet = partner_list.getSheetByName("result");
  if (SS.getFilter() != null && SS.getFilter().toString() == 'Filter') {
    console.log("have populated filter..", SS.getFilter());
    var Avals_pre_D = SS.getFilter().getRange();

    // --- I added below script.
    var url2 = `https://docs.google.com/spreadsheets/d/${partner_list.getId()}/gviz/tq?tqx=out:csv&gid=${SS.getSheetId()}`;
    var res = UrlFetchApp.fetch(url2, {headers: {authorization: `Bearer ${ScriptApp.getOAuthToken()}`}});
    var Avals_pre = Utilities.parseCsv(res.getContentText());
    // ---

    if (targetSheet) {
      partner_list.deleteSheet(targetSheet);
    }
    targetSheet = partner_list.insertSheet();
    targetSheet.setName("result");
    console.log("DATA:", Avals_pre);
    Avals_pre_D.copyTo(
      targetSheet.getRange('A2:R'),
      SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
      false);
    var SS = SpreadsheetApp.openByUrl(same_url).getSheetByName("result"); // aka same.. but the result sheet.
    var Avals_pre = targetSheet.getRange("A2:R" + targetSheet.getLastRow()).getValues();  // Modified
  } else {
    console.log("no filter..");
    var Avals_pre = SS.getRange("A2:R" + SS.getLastRow()).getValues();  // Modified
  }
  console.log(Avals_pre.length);
}

          注意:

          • 在上述修改后的脚本中，通过添加以下脚本，将过滤后的值直接检索到 Avals_pre .

              var url = `https://docs.google.com/spreadsheets/d/${partner_list.getId()}/gviz/tq?tqx=out:csv&gid=${SS.getSheetId()}`;
  var res = UrlFetchApp.fetch(url, {headers: {authorization: `Bearer ${ScriptApp.getOAuthToken()}`}});
  var Avals_pre = Utilities.parseCsv(res.getContentText());

          • 如果您可以使用此脚本，我认为您也可以按照以下说明修改脚本.

          • If you can use this script, I thought that you might be able to also modify your script as follows.

              var partner_list = SpreadsheetApp.openByUrl(url);
  var SS = partner_list.getSheetByName("Sheet 1");
  var Avals_pre;
  if (SS.getFilter() != null && SS.getFilter().toString() == 'Filter') {
    console.log("have populated filter..", SS.getFilter());
    var url2 = `https://docs.google.com/spreadsheets/d/${partner_list.getId()}/gviz/tq?tqx=out:csv&gid=${SS.getSheetId()}`;
    var res = UrlFetchApp.fetch(url2, {headers: {authorization: `Bearer ${ScriptApp.getOAuthToken()}`}});
    Avals_pre = Utilities.parseCsv(res.getContentText());
  } else {
    console.log("no filter..");
    Avals_pre = SS.getRange("A2:R" + SS.getLastRow()).getValues();
  }
  console.log(Avals_pre.length);

            • getLastRow()
            • 相关线程

              关于以下其他问题，

              您知道我如何获得行号吗?etg如果3被过滤掉，我会得到1,2,4吗?

              do you know how i can get the row number? etg if 3 is filtered out, i would get 1,2,4?

              在这种情况下，不能直接使用上述方法.因此，作为一个简单的示例脚本，我添加了另一个示例脚本来检索隐藏行号和显示行号.

              In this case, above method cannot be directly used. So as a simple sample script, I added one more sample script for retrieving the hidden-row numbers and showed-row numbers.

              function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var result = {hiddenRow: [], showedRow: []};
  for (var r = 1; r <= sheet.getLastRow(); r++) {
    result[sheet.isRowHiddenByFilter(r) ? "hiddenRow" : "showedRow"].push(r);
  }
  console.log(result)
}

              • 在此示例脚本中，返回活动工作表的隐藏行号和显示行号.

                • 这篇关于复制到新的工作表上，乘以自己的时间，然后过滤我的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆