How to combine and transpose this dataset using GAS when this is not a tabular dataset yet?-CodePudding

How can this be achieved given the format displyed below?

I see that there are suggestions like this, but it doesn't quite cover the format below.

I barely started it, because I really am not sure where to go from here:

let dates = ["","01/01/2021","02/01/2021","03/01/2021","04/01/2021","05/01/2021","06/01/2021"]

let data = [[1,"Item A","",10,15,9,8,10,15],[2,"Item B","",20,25,35,20,5,15],[3,"Item C","",10,25,24,26,26,1]]

function savedata() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const itemsSheet = ss.getSheetByName('Items');
  const dataRng = itemsSheet.getRange(4, 1, 3, itemsSheet.getLastColumn());
  let dates = itemsSheet.getRange(2, 3, 1, itemsSheet.getLastColumn() - 2).getValues().flat();
  dates = dates.map(e => e !='' ? Utilities.formatDate(new Date(e), Session.getTimeZone(), "MM/dd/yyyy") : '')
  const data = dataRng.getValues();
}

Appreciate any direction!

CodePudding user response：

When your sample values of dates and data are used, how about the following sample script?

Sample script:

function myFunction() {
  // These values are from your question.
  let dates = ["", "01/01/2021", "02/01/2021", "03/01/2021", "04/01/2021", "05/01/2021", "06/01/2021"];
  let data = [[1, "Item A", "", 10, 15, 9, 8, 10, 15], [2, "Item B", "", 20, 25, 35, 20, 5, 15], [3, "Item C", "", 10, 25, 24, 26, 26, 1]];

  // Create an array for putting to the sheet.
  const [, ...d] = dates;
  const [variance, item, , ...values] = data[0].map((_, c) => data.map(r => r[c]));
  const res = d.flatMap((e, i) => variance.map((f, j) => [e, f, item[j], values[i][j]]));

 // The values are put to "Sheet1".
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  sheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

When this script is run, using your provided sample values, your expected values are put to "Sheet1".
I think that in this case, if you want to use the date object of the date, you can directly use the retrieved values by getValues.

Reference:

map()