Google AppScript检查单元格值和删除列

发布于 2025-02-10 10:12:37 字数 1042 浏览 1 评论 0 原文

我目前有一个项目，必须循环并检查3个不同的单元格值。如果值包含＃value1，则应该删除列。我不确定我在做什么错。我不断遇到错误，并且列永远不会删除。另外，我会收到我无法弄清楚的循环错误。

function scoreToGrade() {
  var cellsChecked = ['AF2', 'AG2', 'AH2'];
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var i = 0; i < cellsChecked.length; i++) {
    var value = SpreadsheetApp.getActiveSheet()
      .getRange(cellsChecked[i])
      .getDisplayValues();
    console.log(value);
    if (value == '#VALUE!') {
      // do something
      console.log('Not blank ' + value);
      sheet.deleteColumn(cellsChecked + 1);
    } else {
      // do something else
      console.log('is another value ' + value);
    }
  }
}

日志如下

3:41:23 PM  Notice  Execution started
3:41:23 PM  Info    [ [ 'TR' ] ]
3:41:23 PM  Info    is BlankTR
3:41:23 PM  Info    [ [ 'F' ] ]
3:41:23 PM  Info    is BlankF
3:41:23 PM  Info    [ [ '#VALUE!' ] ]
3:41:23 PM  Info    Not blank #VALUE!
3:41:23 PM  Error   
Exception: Cannot convert 'AF2,AG2,AH21' to int.
scoreToGrade    @ Code.gs:115

原文

I currently have a project where i have to loop through and check 3 different cell values. If the values contain #VALUE1 then it is supposed to delete the column. I am not sure what I am doing wrong. I keep getting an error and column never deletes. Also I get a loop error that I can not figure out.

function scoreToGrade() {
  var cellsChecked = ['AF2', 'AG2', 'AH2'];
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var i = 0; i < cellsChecked.length; i++) {
    var value = SpreadsheetApp.getActiveSheet()
      .getRange(cellsChecked[i])
      .getDisplayValues();
    console.log(value);
    if (value == '#VALUE!') {
      // do something
      console.log('Not blank ' + value);
      sheet.deleteColumn(cellsChecked + 1);
    } else {
      // do something else
      console.log('is another value ' + value);
    }
  }
}

the log is as follows

3:41:23 PM  Notice  Execution started
3:41:23 PM  Info    [ [ 'TR' ] ]
3:41:23 PM  Info    is BlankTR
3:41:23 PM  Info    [ [ 'F' ] ]
3:41:23 PM  Info    is BlankF
3:41:23 PM  Info    [ [ '#VALUE!' ] ]
3:41:23 PM  Info    Not blank #VALUE!
3:41:23 PM  Error   
Exception: Cannot convert 'AF2,AG2,AH21' to int.
scoreToGrade    @ Code.gs:115

分享到QQ

分享到微博

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

发布评论

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

浅紫色的梦幻 2025-02-17 10:12:37

尝试以下操作：

function scoreToGrade() {
  var cellsChecked = ['AF2', 'AG2', 'AH2'];
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var i = 0; i < cellsChecked.length; i++) {
    var value = sheet.getRange(cellsChecked[i]).getDisplayValue();
    console.log(value);
    if (value == '#VALUE!') {
      // do something
      console.log('Not blank ' + value);
      sheet.deleteColumn(sheet.getRange(cellsChecked[i]).getColumn());
    } else {
      // do something else
      console.log('is another value ' + value);
    }
  }
}

Try this:

function scoreToGrade() {
  var cellsChecked = ['AF2', 'AG2', 'AH2'];
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var i = 0; i < cellsChecked.length; i++) {
    var value = sheet.getRange(cellsChecked[i]).getDisplayValue();
    console.log(value);
    if (value == '#VALUE!') {
      // do something
      console.log('Not blank ' + value);
      sheet.deleteColumn(sheet.getRange(cellsChecked[i]).getColumn());
    } else {
      // do something else
      console.log('is another value ' + value);
    }
  }
}

回复收藏 0 原文

一个人的夜不怕黑 2025-02-17 10:12:37

您的错误似乎是在您使用的线路中删除

sheet.deleteColumn(cellsChecked+1);

要将单元格数组传递到此函数的列而不是该方法期望的单个单元格。尝试传递特定数组项目，类似于您在脚本中获得单元格值时所做的类似。

sheet.deleteColumn(cellsChecked[i]);

参考：

问题自发布以来发生了变化

Your error appears to be in the line you are using to delete the columns

sheet.deleteColumn(cellsChecked+1);

You are passing an array of cells to this function instead of the single cell that the method expects. Try passing in a specific array item similar to what you did when getting the value of the cell earlier in your script.