英文:
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);
}
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论