How to unpivot this data right using Google Apps Script?

Question

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: https://docs.google.com/spreadsheets/d/13WmD19BkkM-lpvMhu06SbfkPF-KSdmU9ZltAKc2mxmc/edit#gid=240927939

It's doing it right until the column where we have a date informed. For Usuário and Timestamp, it apparently repeats it all at the bottom and these 2 columns don't come in the first chunck of data.

Appreciate any help, as I can't even find where to begin.

Regards

I don't follow links to google accounts because it often exposes our emails and it also can be difficult to remove links to shared drives. If you wish to share data a better approach is to share images of pertinent sheets and use tables to pass data. Here's a link to script for [creating data tables](https://stackoverflow.com/questions/66772208/a-script-to-simplify-creating-a-so-table) and there are other methods probably as well. — Cooper, Dec 27 '21 at 18:27
First, share your sheet as **public**. Second, try to log the result ``values`` array and compare to what you need. Third, try to implement the ``reduce`` statement into a simpler ``for`` loop and build the result array. — CMB, Dec 27 '21 at 19:14

MattKing · Answer 1 · 2021-12-28T15:37:17.607

FORMULAIC STRATEGY

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))

APPS SCRIPT STRATEGY

Below is some fairly simple code that should accomplish the 'unpivoting' you're after. The simplest way to do it (in my opinion) is to just loop through the inner/repeated columns as it's own for loop (done here with the variable j from 3 to 9), each iteration pushing out a new complete row to a 'finishedValues' variable. You can find the code and test it in the same sample sheet as mentioned above. The output goes to the DB (Script) tab.

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const dataSheet = ss.getSheetByName('Data');
  const dbSheet = ss.getSheetByName('DB (Script)');
  const values = dataSheet.getRange('A6:M').getValues().filter(e=>e[0]);
  const dates = dataSheet.getRange('D4:J4').getValues()[0];
  var finishedValues = [];

  for(let i=0;i<values.length;i++){
    for (let j=3,row=values[i];j<10;j++){
      finishedValues.push([dates[j-3]].concat(row.slice(0,3)).concat(values[i][j]).concat(row.slice(10)))
    }
  }
  //Write the finished values to the lowest row on the DB (Script) tab.
  dbSheet.getRange(dbSheet.getLastRow()+1,1,finishedValues.length,finishedValues[0].length).setValues(finishedValues);
}

Hello! Thanks a lot! The challenge here consists of saving the data from one sheet to the other from time to time. — onit, Dec 27 '21 at 19:48
The idea is that you would have a simple script that would just paste those 'live' values from the MK.Help tab into the bottom of the DB tab whenever you wanted. — MattKing, Dec 27 '21 at 19:54
I'll resort to that, if I can't move forward like this. I am using it to study, as well. Thanks. — onit, Dec 27 '21 at 19:58
@AntonioSantos I've updated my answer (and the sample sheet) with a fairly simple script that seems to work fine. — MattKing, Dec 28 '21 at 15:32

score 1 · Accepted Answer · answered Dec 27 '21 at 20:24

Alternatively, your code is displaying additional rows because your header1 array has trailing empty values:

Info    Header1: Mon Dec 27 2021 00:00:00 GMT-0300 (Argentina Standard Time),Tue Dec 28 2021 00:00:00 GMT-0300 (Argentina Standard Time),Wed Dec 29 2021 00:00:00 GMT-0300 (Argentina Standard Time),Thu Dec 30 2021 00:00:00 GMT-0300 (Argentina Standard Time),Fri Dec 31 2021 00:00:00 GMT-0300 (Argentina Standard Time),Sat Jan 01 2022 00:00:00 GMT-0300 (Argentina Standard Time),Sun Jan 02 2022 00:00:00 GMT-0300 (Argentina Standard Time),,,

You have to filter them out and push the corresponding temporary array values to your result:

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

The first data row should look like this:

I'm sure it's not fair to take your time for this, but would it be possible to ellaborate on this line for learning purposes here? ```srcValues.forEach(([a, b, c, ...dm]) => ar.push([h, a, b, c, dm[i] || 0, dm[dm.length-3], h, dm.pop()]))``` Greatly appreciated! — onit, Dec 27 '21 at 23:53

How to unpivot this data right using Google Apps Script?

2 Answers2