I got the code below, which is not plotting the values as it should.

The idea is to pivot it, in a way that for each date in row 4, we have the set of products, quantities and other information repeated so as to create a DB like table.

function salvarPrevProducao() {
  const srcSheetName = "Previsão Entreposto"; // This is the source sheet name.
  const dstSheetName = "DB"; // Please set the destination sheet name.


  // This is from https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  // 1. Retrieve values from the source sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const lastRow = srcSheet.get1stNonEmptyRowFromBottom(1);
  const [[, , , ...header1], header2, ...srcValues] = srcSheet.getRange("A4:M"   lastRow).getValues();

  Logger.log('Header1: '   header1)
  Logger.log('Header2: '   header2)

  // 2. Create an array for putting to the destination sheet.
  const values = header1.reduce((ar, h, i) => {
    srcValues.forEach(([a, b, c, ...dm]) => ar.push([h, a, b, c, dm[i] || 0, "", dm.pop(), h]));
    return ar;
  }, [["Data", "Tipo", "Cod", "Descrição", "Qtd", "Usuário", "TimeStamp", "Semana"]]);

  // 3. Put the array to the destination sheet.
  const dstSheet = ss.getSheetByName(dstSheetName);
  dstSheet.getRange(dstSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}

Here's what the data looks like prior to processing:

Here's how it should look as the result:

Here's the link to the spreadsheet:

CodePudding user response：

I believe this is more of an 'unpivot' than a pivot.

You want to get pivoted data into database form.

You can use the SPLIT(FLATTEN(... technique for this. See the tab called MK.Help on your shared sheet which will also live here permanently for future users. There you will find this formula: This technique was made possible by the FLATTEN function which as always been available, but was hidden from users and only discovered about 2 years ago. It "flattens" 2d data into one dimension which is ideal for creating Database-like structures out of pivoted data.

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN('Previsão Entreposto'!A6:A&"|"&'Previsão Entreposto'!B6:B&"|"&'Previsão Entreposto'!C6:C&"|"&'Previsão Entreposto'!D6:J&"|"&'Previsão Entreposto'!D5:J5&"|"&'Previsão Entreposto'!D4:J4&"|"&'Previsão Entreposto'!K6:K&"|"&'Previsão Entreposto'!L6:L&"|"&'Previsão Entreposto'!M6:M&"|"&FLOOR('Previsão Entreposto'!D4:J4-2;7) 2);"|";0;0);"select Col6,Col1,Col2,Col3,Col4,Col7,Col8,Col10 where Col4 is not null order by Col6,Col3";0))