按标题值删除列会删除所有列

My code has (at least) 2 problems. Can anyone offer suggestions? I am trying to learn JavaScript so I would really prefer hints rather then completed code. My goal is to copy a sheet Main into a new sheet called achievement and remove all columns that do not have the word achievement in the first row.

我的代码有(至少)2个问题。有人可以提供建议吗?我正在尝试学习JavaScript,所以我更喜欢提示,而不是完成代码。我的目标是将工作表Main复制到名为achievement的新工作表中,并删除第一行中没有成就一词的所有列。

it doesnt copy the values from sheet Main to sheet achievement (I am left with a blank doc in achievement.

它不会将表格中的值复制到工作表的成就(我在成就中留下了一个空白的文档。

When I manually paste the data the code deletes every column (instead of the ones not containing the word achievement)

function myFunction() {
  SpreadsheetApp.getUi().alert('STARTED');
  var search_term='achievement';
  var main=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Main');
  var cp=SpreadsheetApp.getActiveSpreadsheet().insertSheet(search_term);
  SpreadsheetApp.getUi().alert(main.getName()+':'+cp.getName());
  main.copyTo(cp);

  var values = cp.getDataRange().getValues();

  var cell='';
  try{  
    //n=1 skip first col because it has titles as well as the first row
  for(n=1;n<values.length;++n){
    cell = values[0][n] ; // 0 is the index of the first row.
    if(!cell.indexOf('achievement')>-1)
    {
      cp.deleteColumn(n);
      values = cp.getDataRange().getValues();
      n=1
    }
  }
  }catch(e)
  {
    SpreadsheetApp.getUi().alert(e+'val:col:'+cell+':'+n);
  }
  SpreadsheetApp.getUi().alert('DONE');
}

当我手动粘贴数据时,代码删除每一列(而不是不包含单词成就的那些)函数myFunction(){ 用SpreadsheetApp.getUi()警报( '已启动')。 var search_term ='achievement'; var main = SpreadsheetApp.getActiveSpreadsheet()。getSheetByName('Main'); var cp = SpreadsheetApp.getActiveSpreadsheet()。insertSheet(search_term); 。用SpreadsheetApp.getUi()警报(main.getName()+ ':' + cp.getName()); main.copyTo(CP); var values = cp.getDataRange()。getValues(); var cell =''; 尝试{ // n = 1跳过第一列,因为它有标题和第一行为(n = 1时; N - 1) { cp.deleteColumn(N); values = cp.getDataRange()。getValues(); n = 1的 } } }赶上(e)中 { 。用SpreadsheetApp.getUi()警报(E + 'VAL西:' +细胞+ ':' + N); } 。用SpreadsheetApp.getUi()警报( '完成'); } ;>

2 个解决方案

#1

The content of values is a 2-D array, an array of rows, each of them an array of cells (or columns). Your choice of looping range does not match the structure of this data:

值的内容是一个二维数组,一个行数组,每个行都是一个单元格(或列)数组。您选择的循环范围与此数据的结构不匹配:

for(n=1;n<values.length;++n){

This next line has the code equivalent of a double negative in it:

下一行的代码相当于双重否定:

if(!cell.indexOf('achievement')>-1)

... so it will delete the wrong columns. Remember that when the target string isn't present, indexOf() will return -1.

...所以它会删除错误的列。请记住,当目标字符串不存在时,indexOf()将返回-1。

And speaking of deleting columns, consider this:

说到删除列,请考虑以下事项:

cp.deleteColumn(n);

You're looking at pre-fetched data to determine what columns to delete, and deleting them from the spreadsheet. When you delete a column, all columns to the right of the deleted one(s) get new column indexes. In other words, the +/-1 relationship between the pre-fetched arrays and the spreadsheet columns changes.

您正在查看预先获取的数据,以确定要删除的列,并从电子表格中删除它们。删除列时,已删除列右侧的所有列都将获得新的列索引。换句话说,预取数组和电子表格列之间的+/- 1关系会发生变化。

You can avoid that by looping down instead of up.

你可以通过循环而不是向上来避免这种情况。

Otherwise, try stepping through each line in the debugger, validating changes on the spreadsheet. It looks like your new sheet should be populated by main.copyTo(cp);, so check that first.

否则,尝试逐步调试调试器中的每一行,验证电子表格上的更改。看起来你的新工作表应该由main.copyTo(cp);填充,所以先检查一下。

Good luck!

#2

Try this way:

试试这种方式:

 function testFunction()
    {
    var search_term = 'achievement';
    var mainsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Main');
     //make a copy of the main sheet and set a name.
    var values = mainsheet.getDataRange().getValues();
    var columns = mainsheet.getDataRange().getNumColumns();
//Check for each cell value and take the column numbers to an array.

      // Instead of deleting each column from the sheet evrytime in the loop, try modifying the array and then update the sheet with those values one time.   

        }

I can provide the working code if you want.

如果你愿意,我可以提供工作代码。

Hope that helps!

希望有所帮助!

#1