javascript - 使用Google脚本，是否可以将运行函数的结果导出到新工作表中？(Using Google Script, is there a way to export results from running a function into a new sheet?)

Question

Extremely in-experienced coder here, I want to run a regex function that searches for a set of words in a google sheet populated with transcripts, then export, I guess, the results into a new sheet.

(我在这里经验不足的编码员，我想运行一个正则表达式函数，该函数在填充了成绩单的Google工作表中搜索一组单词，然后将结果导出到新工作表中。)

I have a script that does what I want but only shows the results of the regex function in the logs, ie, it pastes the entire transcript that contains the target words.

(我有一个脚本可以执行我想要的操作，但只在日志中显示regex函数的结果，即，它将粘贴包含目标词的整个脚本。)

I would like to show the results in an actual sheet rather than in the logs.

(我想在实际工作表中而不是在日志中显示结果。)

Any feedback would be really helpful, but I would ask you give feedback like you would to a 5 year old, as I said, extremely in-experienced coder here.

(任何反馈都将真正有帮助，但是我想请您像我说的那样对5岁的程序员提供反馈，就像我在这里经验不足的编码器一样。)

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();

var history = ss.getSheetByName('sheet1');
ss.setActiveSheet(history);

for(i = 1; i <= history.getMaxRows(); i += 1) {
  var r = history.getRange('A' + i)

  var regexp = /W*(identity)W*s+(w+s+){0,5}(verification)|(verification)s+(w+s+){0,5}(identity)/


  if (regexp.exec(r.getValue()) !== null ) {
     Logger.log(r.getValue())
    } 

}
}

  ask by Joseph Davitt translate from so

Answer 1

Considering that the mentioned regex works and you have data only in the first column of the sheet, this code will append the values on to a new sheet.

(考虑到上述正则表达式有效并且您仅在工作表的第一栏中有数据，因此此代码会将这些值附加到新工作表上。)

var ss = SpreadsheetApp.getActiveSpreadsheet();

// sheet reference for data
var historySheet = ss.getSheetByName('sheet1');  

// sheet reference for result - create this sheet, if you haven't already
var resultsSheet = ss.getSheetByName('Results');


// get the number of rows which have data in them from 'sheet1'
var totalRowsWithData = historySheet.getDataRange().getNumRows();

// get the data from the rows 
var data = historySheet.getRange(1, 1, totalRowsWithData).getValues(); 

var regexp = /W*(identity)W*s+(w+s+){0,5}(verification)|(verification)s+(w+s+){0,5}(identity)/;
var result = []; // array to store the values

for (var i = 0; i < data.length; i += 1) {
    var row = data[i];
    var column = row[0]; // get the first column value
    if (regexp.exec(column) !== null) {
        result.push(row); // add to the array defined earlier
    }
}

if (result.length > 0) {
    var resultsSheetDataRows = resultsSheet.getDataRange().getNumRows();

    // if no data is present in the result sheet, then start from top. Otherwise add 1 to it
    resultsSheetDataRows = resultsSheetDataRows === 1 ? resultsSheetDataRows : resultsSheetDataRows + 1; 

    // get the range to append the data
    var resultsSheetRange = resultsSheet.getRange(resultsSheetDataRows, 1, result.length);

    // append the data
    resultsSheetRange.setValues(result);
}