移除、删除或将二维数组中的特定元素设置为null。

huangapple go评论71阅读模式
英文:

Remove, delete or turn null specific elements in a 2D array

问题

我正在尝试使用splice方法创建一个从电子表格中当前单元格位置确定的特定位置开始的数组副本,并将其传递给splice函数,以删除指定位置到数组末尾的元素。例如,如果当前列位于第6行第4列,则将删除从该位置开始的数组中的所有元素:

/**
* @customfunction
*/
function custom_function5() 
{

  var spreadSheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/16li86UxKwDl4LUzgEdpfWdbZuDYcZ0t-adNJN56-bzQ/edit?pli=1#gid=0');
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scratch Pad");
  var currentCellRowIndex = SpreadsheetApp.getCurrentCell().getRow();
  Logger.log("The values are " + currentCellRowIndex);
  var currentCellColumnIndex = SpreadsheetApp.getCurrentCell().getColumn();
  Logger.log("The values are " + currentCellColumnIndex);
  var array1 = sheet.getRange(6, 2, currentCellRowIndex, 6).getValues();
  Logger.log("The array values are " + array1);
  var array2 = array1[currentCellRowIndex - 6].splice(currentCellColumnIndex - 1, 6 - currentCellColumnIndex); 
  Logger.log("The array2 values are" + array2);

}

电子表格

我遇到以下错误:ReferenceError: array1 is not defined
1: https://docs.google.com/spreadsheets/d/16li86UxKwDl4LUzgEdpfWdbZuDYcZ0t-adNJN56-bzQ/edit?pli=1#gid=0

英文:

I'm trying to utilize splice to create a copy of an array with the elements from a certain position determined by the position of the current cell in a spreadsheet and pass those on to the splice function to remove the elements from a specified position to the end of the array.
E.g. if the current column is in the 6th row and 4th column, all elements in the array from that position are deleted:

/**
* @customfunction
*/
function custom_function5() 
{

  var spreadSheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/16li86UxKwDl4LUzgEdpfWdbZuDYcZ0t-adNJN56-bzQ/edit?pli=1#gid=0');
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scratch Pad");
  var currentCellRowIndex = SpreadsheetApp.getCurrentCell().getRow();
  Logger.log("The values are " + currentCellRowIndex);
  var currentCellColumnIndex = SpreadsheetApp.getCurrentCell().getColumn();
  Logger.log("The values are " + currentCellColumnIndex);
  var array1 = sheet.getRange(6, 2, currentCellRowIndex, 6).getValues();
  Logger.log("The array values are " + array1);
  var array2 = array1[currentCellRowIndex].splice(currentCellColumnIndex - 1, 6 - currentCellColumnIndex); 
  Logger.log("The array2 values are" + array2);

}

Spreadsheet

I'm getting the following error: ReferenceError: array1 is not defined
1: https://docs.google.com/spreadsheets/d/16li86UxKwDl4LUzgEdpfWdbZuDYcZ0t-adNJN56-bzQ/edit?pli=1#gid=0

答案1

得分: 1

已更新。尝试这个:

function myFunction() {
  var ss = SpreadsheetApp.getActive().getActiveCell();
  var sheet = SpreadsheetApp.getActive().getActiveSheet().getRange("B6:G13");
  var table = sheet.getValues();
  var startRow = 6; //定义起始行
  var startCol = 2; //定义起始列
  var arrayColPosition = ss.getColumn() - startCol;
  var arrayRowPosition = ss.getRow() - startRow;
  var trigger = false;

  console.log("选择的列: " + arrayColPosition, "\n选择的行: " + arrayRowPosition);

  var finalTable = table.map((r, i) => i == arrayRowPosition ? r.map((y, c) => c == arrayColPosition ? y[arrayColPosition] = '*' : y) : r);

  console.log(finalTable);

  var res = finalTable.map((r, indexRow) => r.map((c, indexColumn) => {
    if(c == '*') trigger = true;
    if(indexColumn >= arrayColPosition && trigger || indexRow > arrayRowPosition ){
      return '';
    }else{
      return c;
    }
  }));

  sheet.setValues(res);
}

活动单元格将设置一个占位符 "*",然后从那里删除表格中的其余数据。

英文:

UPDATED. Try this:

  function myFunction() {
  var ss = SpreadsheetApp.getActive().getActiveCell();
  var sheet = SpreadsheetApp.getActive().getActiveSheet().getRange("B6:G13");
  var table = sheet.getValues();
  var startRow = 6; //define start row
  var startCol = 2; //define start col
  var arrayColPosition = ss.getColumn() - startCol;
  var arrayRowPosition = ss.getRow() - startRow;
  var trigger = false;

  console.log("Selection Column: "+arrayColPosition, "\nSelection Row: "+arrayRowPosition);

  var finalTable = table.map((r, i) => i == arrayRowPosition ? r.map((y, c) => c == arrayColPosition ? y[arrayColPosition] = '*' : y) : r);

  console.log(finalTable);

  var res = finalTable.map((r, indexRow) => r.map((c, indexColumn) => {
    if(c == '*') trigger = true;
    if(indexColumn >= arrayColPosition && trigger || indexRow > arrayRowPosition ){
      return '';
    }else{
      return c;
    }
  }));

  sheet.setValues(res);
}

The active cell will set a placeholder " * " and from there it will delete the remaining data on the table.

GIF:

移除、删除或将二维数组中的特定元素设置为null。

huangapple
  • 本文由 发表于 2023年7月11日 04:22:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76657084.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定