在不使用循环的情况下从Google Spreadsheet查找具有特定值的行? [英] Find rows with particular value from a Google Spreadsheet without using loop?

问题描述

我是电子表格脚本的新手.我正在根据每天输入值的另一个工作表生成报告(新工作表).在Apps脚本中，我首先生成工作表，然后遍历从该输入工作表中检索到的数据范围. 之后，我必须根据日期和类别合并值.
现在，我的报告格式是:行是类别，日期是列. 因此，如果在输入中还有另一个具有相同日期和相同类别的值，则必须添加该值.

I am new to spreadsheet scripting. I am generating a report (new sheet) based on another sheet where I enter values daily. In Apps Script I first generate the sheet then loop through the data range retrieved from that input sheet. After that I have to merge values based on dates and categories.
Now my report format is such that rows are categories and dates are columns. So if in input if there is another value with same date and same category I have to add the value.

我的问题是如何检查报表中是否存在具有相同日期和类别的值，并且我不希望使用循环，因为我已经处于循环状态，这样会使处理过程非常缓慢.

My problem is how to check if the value with same date and category exists in the report and I DO NOT want to use loops as I am already in loops so that will make the process run very very slow.

推荐答案

您的报告听起来有点像数据透视表，其中行类别，列日期和SUM(Value)作为数据字段.要在脚本报告中重现此内容，可以使用Object变量，该变量在键和值" Object:

Your report sounds a fair bit like a Pivot Table with categories in rows, dates in columns, and SUM(Value) as the data field. To reproduce this with a script report, you can use an Object variable that maps between a key and a "value" Object:

这可能不是您的确切用例(假设您需要从可能很大的供稿器数据堆栈中生成新报告，但是它应演示如何使用嵌套的Object来简化/内部化查找过程，包括测试未定义的值和强制执行矩形输出.

This probably isn't your exact use case (it assumes you need to generate a new report from a possibly-large stack of feeder data, but it should demonstrate how you can use nested Objects to simplify / internalize the lookup process, including testing for undefined values and enforcing a rectangular output.

// Make an array of the data, to limit use of the slow spreadsheet interface.
var inputs = SpreadsheetApp.openById(<id>).getSheetByName(<dataSheetName>)
    .getDataRange().getValues();
// The first row is probably column headers from the input sheet, and
// doesn't likely contain useful data that you want in your report.
var headers = inputs.splice(0, 1);
var report = {};
for(var row = 0; row < inputs.length; ++row) {
    // Change these indexes (0, 1, 2) to the proper values.
    // Also do any necessary formatting / validation, etc. for "category" and "date".
    var category = inputs[row][0];
    var date = inputs[row][1];
    var value = inputs[row][2];
    // If this category doesn't exist, default construct its report object.
    // For each category, a nested object is used to store the date-value pair.
    if(!report[category]) {
        report[category] = {};
    }
    // Otherwise, if the date is not yet seen for the category, set
    // the value. If it is seen, increment the stored value by the new value.
    if(!report[category][date]) {
        report[category][date] = value;
    } else {
        // Treat this as numeric addition, not string concatenation.
        report[category][date] += value - 0;
    }
}
// To print your report, you need a header you can index against.
var outputHeader = [];
for(var category in report) {
    for(var date in category) {
        outputHeader.push(date);
    }
}
// Sort this header row. If the dates are strings that don't simply
// coerce to proper Date objects, you'll need to write your own sort() method.
// (You don't technically need to sort, but if you don't then the dates
// won't be "in order" when the report prints.)
outputHeader.sort();
// After sorting, add a row label for the header of sorted dates.
outputHeader.splice(0, 0, "Category / Date");

// Serialize the report object into an array[][];
var output = [outputHeader];
var totalColumns = outputHeader.length;
for(var category in report) {
    // Initialize each row with the row label in the 0 index position.
    var row = [category];
    for(var date in category) {
        var index = outputHeader.indexOf(date);
        row[index] = category[date];
    }
    // Unless you are guaranteed that every category has a value for every date
    // in the report, you need to ensure that the row has a value at each index.
    // (This is a good idea anyway, to ensure that you have a rectangular array.)
    var filled = Object.keys(row);
    // We can start at 1 since we know that every row starts with its category.
    for(var col = 1; col < totalColumns; ++col) {
        if(filled.indexOf(String(col)) < 0) {
            row[col] = "";
        }
    }
    output.push(row);
}
SpreadsheetApp.openById(<id>).getSheetByName(<reportSheetName>)
    .getRange(1, 1, output.length, output[0].length).setValues(output);

这篇关于在不使用循环的情况下从Google Spreadsheet查找具有特定值的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！