Выполнение нескольких SUMIFS - Редактор сценариев Google Sheet

ProgramBox

Выполнение нескольких SUMIFS — Редактор сценариев Google Sheet

Post author:admin
Запись опубликована:19 мая, 2023
Post category:Вопросы по программированию

#javascript #google-apps-script

#javascript #google-приложения-скрипт

Вопрос:

Я пытаюсь выполнить SUMIFS в редакторе сценариев Google Sheet

То, что я пытаюсь сделать, это…

У меня есть эта необработанная таблица.

Я пытаюсь создать отчет, который выглядит следующим образом .. с соответствующими значениями (из этой необработанной таблицы)

Итак, я использовал SUMIFS (значение) SUMIFS (значение) для этого.

Есть ли способ выполнить ту же работу в редакторе сценариев?

Ответ №1:

Я написал test функцию для чтения АКТИВНЫХ значений листа и записи отчетов разных поставщиков на отдельных листах. Если вы хотите узнать больше о моем коде, дайте мне знать в комментарии.

 function test() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  // remove header row values from array
  values.shift();

  // will be months present in data
  // { JAN: 1, FEB: 1 ... }
  var mos = {};
  // data structure
  // { A: {JAN: [value1, value2] }... }
  var ds = {};
  values.forEach(function(row) {
    var month = row.shift(); // 1st col
    var ven = row.shift(); // 2nd col
    mos[month] = 1; // set month on mos
    ds[ven] = ds[ven] || {};
    // set value on ds
    // example = ds.A.JAN = [ A B, C D ]
    ds[ven][month] = [parseInt(row[0])   parseInt(row[1]), parseInt(row[2])   parseInt(row[3])];
  });
  //  Logger.log(mos);
  var months = ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'];
  // filter months to get months present in sheet data in order
  months = months.filter(function(m) {
    return mos[m] == 1;
  });
  // Logger.log(months);
  // result object, for each vendor create array of rows
  // { A: [ row, row... ]... }
  var rs = {};
  Object.keys(ds).forEach(function(v) {
    rs[v] = rs[v] || [];
    months.forEach(function(m) {
      if (ds[v][m]) {
        rs[v].push([m, ds[v][m][0], ds[v][m][1]]);
      } else {
        rs[v].push([m, 'N/A', 'N/A']);
      }
    });
  });
  // Logger.log(rs);
  // write rows
  Object.keys(rs).forEach(function(v) {
    var headers = [['Vendor', v, 'Report'].join(' '), 'Conversion (A B)', 'Conversion (C D)'];
    // insert headers into rows for writing
    rs[v].unshift(headers);
    try {
      ss.insertSheet(headers[0]);
    } catch (e) {}
    var sh = ss.getSheetByName(headers[0]);
    sh.clear();
    sh.getRange(1, 1, rs[v].length, 3).setValues(rs[v]);
  });
}

1. Это потрясающе! Это именно то, что я искал.. Хотя сейчас пытаюсь понять каждую строку. Я оставлю комментарий, если у меня возникнут вопросы..

2. о, для этого кода values.shift(), зачем это необходимо? сделано ли это, чтобы установить месяц и столбец поставщика равными 1,2 для каждого? не 0,1?

3. Я прочитал все строки в sheet, но строка 1 является строкой заголовка, поэтому shift () удаляет эту строку из значений, сохраняются только строки значений. Также я отредактировал приведенный выше код, чтобы добавить еще несколько комментариев для вашего понимания.

4. Вы можете включить Logger.log() строки, чтобы видеть, что регистрируется. Это также даст вам представление об используемых структурах данных.

5. Привет, ra89fi, ты не возражаешь, если я задам тебе еще один вопрос? — аналогично, но в более сложном формате ..?

Ответ №2:

Существует распространенная проблема, связанная с агрегатами

 /**
 * @customfunction
 */
function BLAHBLAHFUNCTION(range) {
  var header = range.shift();
  var vendors = range.map(function(row){return row[1];});
  var r =  range.reduce(function(p, row) {
    if (!p.hasOwnProperty(row[0])) { 
      p[row[0]] = {};
      vendors.forEach(function(vendor){
        if (!p[row[0]].hasOwnProperty(vendor))
          p[row[0]][vendor] = { AB: 0, CD: 0 };
      });
    }
    p[row[0]][row[1]].AB  = row[2]   row[3];
    p[row[0]][row[1]].CD  = row[4]   row[5];
    return p;
  }, {});

  var res = [header.slice(0,2).concat('AB', 'CD')];
  for(var month in r){
    for(vendor in r[month]){
      res.push([month, vendor, r[month][vendor].AB, r[month][vendor].CD]);
    }
  }
  return res;
}

Мой пример https://docs.google.com/spreadsheets/d/1yo627ZkT7zG7_Aj86Ww0iqUTd2o1xCAY_hDWk9DPsao/edit?usp=sharing