检查一个区域的列标题，并对另一个区域的单元格进行操作

Question

我有两个命名的范围;DbCrHdr,Transactions我想通过Transactions分析单元格的列索引或列索引是否匹配"credit"在DbCrHdr数组上，然后我想将该数字转换为负数或乘以-1，并将该数组重新映射/重新馈送回Transactions，改变工作表。

我怎样才能做到这一点?

Google工作表文件

附加的Google apps脚本文件;

/** @OnlyCurrentDoc */
// Global Variables
// Set ss to active spreadsheet 
var SS = SpreadsheetApp.getActiveSpreadsheet();
// Set sheet to the current active sheet 
var Sheet = SS.getActiveSheet();
// Get current sheet name and set it to sheetName  
var SheetName = Sheet.getName();
// Get range by name and set it to a global variable
var TransxnRange = SpreadsheetApp.getActive().getRangeByName("Transactions");
// Get array of values in the search Range
var TransxnValues = TransxnRange.getValues();
// Get range by name and set it to a global variable
var DbCrRange = SpreadsheetApp.getActive().getRangeByName("DbCrHdr");
// Get array of values in the search Range
var DbCrValues = DbCrRange.getValues();
/**
* Parse through the debit-credit row and change the currency to negative if the header is credit.
* 
* 
*/
function NegativeCredit()  {
var LastCol = TransxnRange.getLastColumn();
var LastRow = TransxnRange.getLastRow();
var NumCol = TransxnRange.getNumColumns();
var NumRow = TransxnRange.getNumRows();

/**
Logger.log(LastCol)
Logger.log(LastRow)
Logger.log(NumCol)
Logger.log(NumRow)
Logger.log(TransxnRange.getA1Notation())
Logger.log(TransxnValues)
Logger.log(DbCrRange.getA1Notation())
Logger.log(DbCrValues)
*/

TransxnValues.forEach(function(row) {
row.forEach(function(col) {
// Logger.log(col);  
});
});
for ( i = 0; i < NumCol; i++) {
for ( j = 0; j < NumRow; j++) {
Logger.log(TransxnValues[j][i])
}
}
}

Answer 1

说明:

您的目标是找到credit出现在DbCrValues的第一行的列索引:

creditIndexes = DbCrValues.flat().flatMap((cr, i) => cr === 'credit' ? i : []);

然后forEach列索引在creditIndexes检查每一行

如果行是数字且值为正数，则将符号更改为负号:

creditIndexes.forEach(col=>{
TransxnValues.forEach((row,ir)=>{  
let val = row[col];
TransxnValues[ir][col] = val > 0 && !Number.isNaN(val) ? -val : val;
});
});

否则，保持现有值。

解决方案:

保留全局声明，并将NegativeCredit()更改为:

function NegativeCredit()  {
const creditIndexes = DbCrValues.flat().flatMap((cr, i) => cr === 'credit' ? i : []);
creditIndexes.forEach(col=>{
TransxnValues.forEach((row,ir)=>{  
let val = row[col];
TransxnValues[ir][col] = val > 0 && !Number.isNaN(val) ? -val : val;
});
});
TransxnRange.setValues(TransxnValues);  
}

额外的引用:

• 平
• flatMap
三目运算符