Google 电子表格查询:如何停止显示范围作为结果?

发布于 2024-10-04 12:42:19 字数 831 浏览 3 评论 0原文

晚上好,

我正在尝试使用一些编码技能来帮助操作 Google Docs 中的电子表格,并认为我制定的逻辑是合理的 - 但脚本仅将结果返回为“范围”。

本质上,该脚本是一个嵌套循环,并且(应该)从一行数据中的六个点(从 11 开始)获取值,并将它们放入一个长垂直线(第 38 列)。然后它应该移动到下一行。

我认为它有效,但结果只是以“范围”形式返回,并且看不到如何将值放入范围(如果这就是这个意思)。

我还意识到,使用单个数组来收集单个数组上的数据可能更有效,但我仍在尝试掌握语法。

这是我的代码:

function Transform() {
  //load spreadsheet data and initialise variables

  var sheet = SpreadsheetApp.getActiveSheet();
  var firstrow = 11;//this is a manual figure
  var d = firstrow;
  var valuerplc = sheet.getRange(firstrow, 38);
  var value =1;

  for(var c=firstrow; c<sheet.getLastRow(); c++){
    for (var e=3; e<33; e=e+6){
      var mon = sheet.getRange(c, e);
      valuerplc = sheet.getRange(d, 38);
      valuerplc.setValue(mon);

    }
    d++;

   }
  }

任何人都可以帮忙,或者至少指出我正确的方向吗?

Good evening,

I'm trying to use some coding skills to help manipulate a spreadsheet in Google Docs and think the logic I've worked out is sound - but the script just returns results as 'Range'.

Essentially the script is a nested loop and (should) take values from six points in a row of data (starting in 11) and plonk them into a long vertical (column 38). Then it should move onto the next row.

I think it works, but the results just come back as 'Range' and can't see how to put the values into range, if that's what this means.

I also realise that it might be more effective to use a single array to gather the data on an individual array, but I'm still trying to get to grips with the syntax.

Here's my code:

function Transform() {
  //load spreadsheet data and initialise variables

  var sheet = SpreadsheetApp.getActiveSheet();
  var firstrow = 11;//this is a manual figure
  var d = firstrow;
  var valuerplc = sheet.getRange(firstrow, 38);
  var value =1;

  for(var c=firstrow; c<sheet.getLastRow(); c++){
    for (var e=3; e<33; e=e+6){
      var mon = sheet.getRange(c, e);
      valuerplc = sheet.getRange(d, 38);
      valuerplc.setValue(mon);

    }
    d++;

   }
  }

Can anyone help, or at least point me in the right direction, please?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

゛时过境迁 2024-10-11 12:42:19

您遇到了一个常见的新手问题,无法区分范围和值。

范围表示电子表格的一个区域,并使用可让您访问对象的各种特征的方法。您在电子表格中看到的内容或数据可通过 .getValue() 方法(对于一个单元格)或 .getValues() (对于多个单元格)进行访问)。

将:更改

var mon = sheet.getRange(c, e);

为:

var mon = sheet.getRange(c, e).getValue();

...,您将获得该范围内的数据,作为字符串、数字或日期,具体取决于其中的内容。

您选择的变量名称可能会导致混乱,因此值得小心。例如,查看 valuerplc。顾名思义,它是一个值,可能会与 .getValue().setValue() 混淆。但它的使用方式是作为一个范围,如 valuerplc.setValue(mon) 中所示,所以它显然不是一个值。

function Transform() {
  //load spreadsheet data and initialise variables

  var sheet = SpreadsheetApp.getActiveSheet();
  var firstrow = 11;//this is a manual figure
  var d = firstrow;
  var valuerplc = sheet.getRange(firstrow, 38);
  var value =1;

  for(var c=firstrow; c<sheet.getLastRow(); c++){
    for (var e=3; e<33; e=e+6){
      var mon = sheet.getRange(c, e).getValue();
      valuerplc = sheet.getRange(d, 38);
      valuerplc.setValue(mon);
    }
    d++;
  }
}

You've been caught with a common newbie problem, having trouble differentiating between Ranges and Values.

A Range expresses a region of a spreadsheet, with methods that give you access to various characteristics of the object. The contents, or data that you see in a spreadsheet is accessible via the .getValue() method (for one cell) or .getValues() (for more than one cell).

Change:

var mon = sheet.getRange(c, e);

To:

var mon = sheet.getRange(c, e).getValue();

... and you'll have the data that's in that range, as a String, Number or Date, depending on what was there.

The variable names you've selected may lead to confusion, it's worthwhile being careful with that. For example, look at valuerplc. The name implies that it's a value, which can be confused with .getValue() and .setValue(). But the way that it's used is as a Range, as in valuerplc.setValue(mon), so it's clearly NOT a value.

function Transform() {
  //load spreadsheet data and initialise variables

  var sheet = SpreadsheetApp.getActiveSheet();
  var firstrow = 11;//this is a manual figure
  var d = firstrow;
  var valuerplc = sheet.getRange(firstrow, 38);
  var value =1;

  for(var c=firstrow; c<sheet.getLastRow(); c++){
    for (var e=3; e<33; e=e+6){
      var mon = sheet.getRange(c, e).getValue();
      valuerplc = sheet.getRange(d, 38);
      valuerplc.setValue(mon);
    }
    d++;
  }
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文