我试图运行一些脚本来查找各种单元格值,返回包含搜索值的单元格的每个列号,然后根据每次返回值 setValue .我已经得到以下示例:
function superAuto(){var report = SpreadsheetApp.openById('REPORTSHEET');var reportData = report.getDataRange().getValues();var scorecard = SpreadsheetApp.openById('SCORESHEET');var scorecardData = scorecard.getDataRange().getValues();var column = columnfinder();scorecard.getRange(18,[column]).setValue(示例输入");}函数columnfinder(){var report = SpreadsheetApp.openById('REPORTSHEET');var reportData = report.getDataRange().getValues();for(var j = 0,jLen = reportData.length; j< jLen; j ++){for(var k = 0,kLen = reportData [0] .length; k有什么想法吗?
解决方案如果要 columnfinder()返回列索引列表,则需要定义一个数组变量,该变量将保存匹配的列索引,然后返回其值.您可以使用
得分表:
Am trying to run a bit of script to find various cell values, return each of the column numbers of the cells that contain the searched-for values, and then setValue according to each return. I've got as far as the below example:
function superAuto() { var report = SpreadsheetApp.openById('REPORTSHEET'); var reportData = report.getDataRange().getValues(); var scorecard = SpreadsheetApp.openById('SCORESHEET'); var scorecardData = scorecard.getDataRange().getValues(); var column = columnfinder (); scorecard.getRange(18, [column]).setValue("Example input"); } function columnfinder() { var report = SpreadsheetApp.openById('REPORTSHEET'); var reportData = report.getDataRange().getValues(); for(var j=0, jLen=reportData.length; j<jLen; j++) { for(var k=0, kLen=reportData[0].length; k<kLen; k++) { var find = "Nov-20" || "Dec-20" || "Jan-21"; if(find == reportData[j][k]) { Logger.log(k); return (k);} } } }The problem with my code as it stands is that it only returns k for one of the search terms (the first one, "Nov-20"), and therefore only setValue's one cell in SCORESHEET based on that, whereas as by this example I'd really like 3 cells to be found in REPORTSHEET (all the search terms are there to be found), 3 values of k to be returned, and therefore 3 cells to be populated in SCORESHEET.
Any thoughts?
解决方案If you want columnfinder() to return a list of column indexes, you need to define an array variable that will hold the matched column indexes before returning its value. You can use array.push() to append values to your array.
Your code: function superAuto() { var report = SpreadsheetApp.openById('REPORTSHEET'); var reportData = report.getDataRange().getValues(); var scorecard = SpreadsheetApp.openById('SCORESHEET'); var scorecardSheet = scorecard.getSheetByName('Sheet1'); var scorecardData = scorecard.getDataRange().getValues(); var columns = columnfinder(); Logger.log(columns); columns.forEach(col=>{ scorecardSheet.getRange(18, col).setValue("Example input"); }) } function columnfinder() { var report = SpreadsheetApp.openById('REPORTSHEET'); var reportData = report.getDataRange().getValues(); var result = []; for(var j=0, jLen=reportData.length; j<jLen; j++) { for(var k=0, kLen=reportData[0].length; k<kLen; k++) { var find = ["Nov-20", "Dec-20", "Jan-21"]; if(find.includes(reportData[j][k])) { //return (k); //Array is in zero-based, but column index for sheet range is in one-based. result.push(k+1); } } } return result; }
Modifications Done:
I declared a result array in columnfinder() that will hold column indexes and push found indexes using in result.push(k+1);
Replaced the condition in matching strings in columnfinder(), I declared an array find and used array.includes() and check if find array contains the current cell being checked.
Return the result after finishing the loop in columnfinder()
Added a scorecardSheet variable in superAuto(). Initially you are trying to use getRange(row, column) in the spreadsheet scorecard which is not possible. (See Spreadsheet methods). getRange(row, column) is available in Sheet Class
Output:
Report Sheet:
Score Sheet:
更多推荐
如何为setValue返回多个列值
发布评论