Начну с теории 🙂

Шахматная ведомость

Что такое шахматная ведомость? Это термин из бухгалтерии, обозначающий таблицу, в которой в горизонтальных строках указаны записи по дебетуемым счетам, а в вертикальных колонках — записи по кредитуемым счетам

Согласен, непонятно еще больше.

Если по простому, есть некая аналитика по строкам и кросс-аналитика по столбцам. Штука, для любого бухгалтера, абсолютно понятная, но в дальнейшем использовании жутко неудобная.

На что похожа шахматная ведомость? На стандартный отчет из любого маркетплейса. Ну например вот такой:

Где по каждому сотруднику по датам видны его результаты.

Что опять же для дальнейшего анализа не совсем удобно. Ни запросы на основе QUERY() построить, ни SUMIF() для свода сделать.

Flat file

Плоский (flat) файл — это набор данных, хранящихся в двумерной базе данных, в которой похожие, но дискретные строки информации хранятся в виде записей в таблице.

Применительно к отчету выше, для анализа данных удобнее использовать плоские (flat) данные, которые по своей структуре напоминают таблицу с записями, например «развернутые» вертикально по Иванову.

Как «развернуть»?

Исходя из отчета нужен срез по данным, чтобы к каждому числовому значению было свое текстовое значение из шапки:

в следующем виде:

и далее, чтобы к каждой строке относилась аналитика по сотруднику и дате:

Значит задача разбивается на 3 этапа:

• получить массив с данными
• получить массив с аналитикой
• вернуть объединенный массив

function flatData(dataArr, analyticsArr) {
  const dataArray = flatDataArray(dataArr)
  const headerArray = flatHeaderArray(analyticsArr)
  return mergedArray(dataArray, headerArray)
}

Обрабатываю массив с данными

function flatDataArray(arr) {
  const arrHeader = arr.shift()
  const objArr = arr.map(row => {
    let obj = {}
    arrHeader.forEach((h, i) => {
      obj[h] = row[i]
    })
    return obj
  })
  return objArr.map(row => Object.entries(row))
}

Что здесь происходит?

const arrHeader = arr.shift() — массив с шапкой таблицы

Далее разворачиваю массив в объект где каждое поле шапки как ключ и каждое поле данных как значение. После делаю из объекта ключ-значение обратно массив через objArr.map(row => Object.entries(row))

Обрабатываю массив с аналитикой

function flatHeaderArray(arr) {
  arr.shift()
  return arr
}

Тут просто убираю шапку из массива где указаны даты и фамилии

Объединяю массивы

function mergedArray(firstArray, secondArray) {
  const finalArray = [];
 
 firstArray.forEach((elem, index) => {
    let arr = [...elem]
    arr.forEach(arrElem => {
      finalArray.push([...arrElem, ...secondArray[index]])

    })
  })
  return finalArray;
}

Тут самое сложное это не запутаться в деструктуризации вида [...elem] и ...secondArray[index] потому что вложенность двух массивов в массивах — разная.

Тема деструктуризации массива/массивов не совсем простая и, если, будет интересно, я сделаю по ней отдельную запись.

Добавляю описание для пользовательской функции

/**
 * Возвращает горизонтальные данные в виде вертикальной таблицы.
 * @param dataArr -  массив с данными " к развороту".
 * @param analyticsArr -  массив с данными для аналитики.
 * @customfunction
*/

Описание необходимо в тот момент, когда функция вызывается:

Вызываю функцию на листе и передаю два массива

в результате получая плоскую таблицу с данными, удобными для анализа:

Скрипт полностью

/**
 * Возвращает горизонтальные данные в виде вертикальной таблицы.
 * @param dataArr -  массив с данными " к развороту".
 * @param analyticsArr -  массив с данными для аналитики.
 * @customfunction
*/
function flatData(dataArr, analyticsArr) {
  const dataArray = flatDataArray(dataArr)
  const headerArray = flatHeaderArray(analyticsArr)

  return mergedArray(dataArray, headerArray)

}

function flatDataArray(arr) {
  const arrHeader = arr.shift()
  const objArr = arr.map(row => {
    let obj = {}
    arrHeader.forEach((h, i) => {
      obj[h] = row[i]
    })
    return obj
  })
  return objArr.map(row => Object.entries(row))
}


function flatHeaderArray(arr) {
  arr.shift()
  return arr
}


function mergedArray(firstArray, secondArray) {
  const finalArray = [];
  firstArray.forEach((elem, index) => {
    let arr = [...elem]
    arr.forEach(arrElem => {
      finalArray.push([...arrElem, ...secondArray[index]])

    })
  })
  return finalArray;
}

---

Ваше мнение важно и может улучшить блог

Я хочу услышать ваше мнение и ваши идеи о том, как сделать этот сайт еще лучше. Примите участие в опросе, чтобы поделиться вашими пожеланиями, предложениями и замечаниями. Пройдите опрос сейчас и помогите сделать этот сайт более полезным для вас!