changing date format returned in custom function - google sheets/ appscript

Question

I've been using this script I adjusted from a previous question
Google Sheets GROUPBY Query that combines values into one cell to satisfy grouped columns
and it has been working well except that I started using it on a new sheet and the date format now shows up as
"Tue May 03 2022 00:00:00 GMT-0800 (GMT-08:00) - 381.1
Wed May 04 2022 00:00:00 GMT-0800 (GMT-08:00) - 70.7"
instead of what it was formerly showing up as -
"5/3/22 - 381.1
5/4/22 - 70.7"
I'm sure this has something to do with the date format in the cells of the source data, but I can't change those because they are pulling from other documents and comparing data.
Here's a test sheet I made: https://docs.google.com/spreadsheets/d/1etBO4hsrvmi_rw6F_Bw7T4DgLzHvfueIHdKa6wezWjA/edit#gid=1539515971
And here's the script, the function is called in A2 of the sheet "Master":

function createMaster(source) {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName(source);
  const sourceArray = sourceSheet.getRange(2,1, sourceSheet.getLastRow(), 14); // Get source data
  const sourceValues = sourceArray.getValues()
  const groups = sourceValues.map(row => { // Get unique combinations
    return JSON.stringify([row[0]].concat(row.slice(2,6)).concat(row.slice(9,10)));
  }).reverse().filter((e, i, a) => a.indexOf(e, i + 1) === -1)
    .reverse().map(JSON.parse);
  let groupedData = groups.map(group => {
    const groupRows = sourceValues.filter(row => { // Get matching rows
      return JSON.stringify([row[0]].concat(row.slice(2,6)).concat(row.slice(9,10))) === JSON.stringify(group);
    });
    return groupRows.reduce((acc, current) => { // Adding the values from same combination
      const date = acc[1] === "" ? current[1] + " - " + current[8]: acc[1] + "\n" + current[1] + " - " + current[8];
      const caseType = acc[6] === current[6] ? acc[6] : acc[6] + "\n" + String(current[6]);
      const freshFrozen = acc[5] === current[5] ? acc[5] : acc[5] + "\n" + String(current[5]);
      const aggregate = acc[10] === current[10] ? acc[10] : acc[10] + "\n" + String(current[10]);
      const location = acc[13] === "" ? current[13] : acc[13] + "\n" + current[13];
      const pallet = acc[11] === current[11] ? acc[11] : acc[11] + "\n" + String(current[11]);
      const unit = (Number(acc[7]) + Number(current[7]) > 0) ? (Number(acc[7]) + Number(current[7])) : 1;
      const weight = Number(acc[8]) + Number(current[8]);
      const comments = acc[12] + "\n" + current[12];
      return [group[0], date, ...group.slice(1,4), freshFrozen, caseType, unit, weight, group[5], aggregate, pallet, comments, location];
    }, Array(14).fill(""));
  });
  groupedData.forEach(row => { // Removing duplicate dates, case types, locations
    row[1] = [...new Set(row[1].split("\n"))].join("\n");
    row[5] = [...new Set(row[5].split("\n"))].join("\n");
    row[6] = [...new Set(row[6].split("\n"))].join("\n");
    row[11] = [...new Set(row[11].split("\n"))].join("\n");
    row[12] = [...new Set(row[12].split("\n"))].join("\n");
    row[13] = [...new Set(row[13].split("\n"))].join("\n");
  });
  return groupedData;
}

Answer 1

try the code below:

function createMaster(source) {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName(source);
  const sourceArray = sourceSheet.getRange(2, 1, sourceSheet.getLastRow(), 14); // Get source data
  const sourceValues = sourceArray.getValues()
  const groups = sourceValues.map(row => { // Get unique combinations
    return JSON.stringify([row[0]].concat(row.slice(2, 6)).concat(row.slice(9, 10)));
  }).reverse().filter((e, i, a) => a.indexOf(e, i + 1) === -1)
    .reverse().map(JSON.parse);
  let groupedData = groups.map(group => {
    const groupRows = sourceValues.filter(row => { // Get matching rows
      return JSON.stringify([row[0]].concat(row.slice(2, 6)).concat(row.slice(9, 10))) === JSON.stringify(group);
    });
    return groupRows.reduce((acc, current) => { // Adding the values from same combination
    //Added codes to convert object to string and change date format *** START
      if (typeof(current[1]) != "string"){
        var dateString = current[1].toDateString();
        var formattedDate = Utilities.formatDate(new Date(dateString), "SAST", "MM/dd/YY");
      }
      var date = acc[1] === "" ? formattedDate + " - " + current[8] : acc[1] + "\n" + formattedDate + " - " + current[8];
      if (date == "undefined - "){
        date = " - ";
      }
      //Added codes to convert object to string and change date format ***END
      const caseType = acc[6] === current[6] ? acc[6] : acc[6] + "\n" + String(current[6]);
      const freshFrozen = acc[5] === current[5] ? acc[5] : acc[5] + "\n" + String(current[5]);
      const aggregate = acc[10] === current[10] ? acc[10] : acc[10] + "\n" + String(current[10]);
      const location = acc[13] === "" ? current[13] : acc[13] + "\n" + current[13];
      const pallet = acc[11] === current[11] ? acc[11] : acc[11] + "\n" + String(current[11]);
      const unit = (Number(acc[7]) + Number(current[7]) > 0) ? (Number(acc[7]) + Number(current[7])) : 1;
      const weight = Number(acc[8]) + Number(current[8]);
      const comments = acc[12] + "\n" + current[12];

      return [group[0], date, ...group.slice(1, 4), freshFrozen, caseType, unit, weight, group[5], aggregate, pallet, comments, location];
    }, Array(14).fill(""));
  });
  groupedData.forEach(row => { // Removing duplicate dates, case types, locations
    row[1] = [...new Set(row[1].split("\n"))].join("\n");
    row[5] = [...new Set(row[5].split("\n"))].join("\n");
    row[6] = [...new Set(row[6].split("\n"))].join("\n");
    row[11] = [...new Set(row[11].split("\n"))].join("\n");
    row[12] = [...new Set(row[12].split("\n"))].join("\n");
    row[13] = [...new Set(row[13].split("\n"))].join("\n");
  });
  return groupedData;
}

I have slightly tweaked your code. Currently your date is an object so it has to be converted to date string then apply your desired format which is MM/dd/YY, that's what the code I've added does.

Here's the result:

Reference:

• https://webapps.stackexchange.com/questions/73701/conversion-from-object-to-string-in-google-apps-script

Answer 2

I believe your goal is as follows.

• You want to 5/3/22 - 381.1\n5/4/22 - 70.7 instead of Tue May 03 2022 00:00:00 GMT-0800 (GMT-08:00) - 381.1\nWed May 04 2022 00:00:00 GMT-0800 (GMT-08:00) - 70.7 in your script.

If my understanding is correct, how about the following modification?

From:

return groupRows.reduce((acc, current) => { // Adding the values from same combination
  const date = acc[1] === "" ? current[1] + " - " + current[8]: acc[1] + "\n" + current[1] + " - " + current[8];

To:

return groupRows.reduce((acc, current) => { // Adding the values from same combination

  current[1] = current[1] instanceof Date ? Utilities.formatDate(current[1], ss.getSpreadsheetTimeZone(), "dd/MM/yy") : current[1]; // Added

  const date = acc[1] === "" ? current[1] + " - " + current[8]: acc[1] + "\n" + current[1] + " - " + current[8];

Testing:

When this modification is used for your script and your provided Spreadsheet, the following result is obtained.

From:

To:

Note:

• From your question, I used MM/dd/yy as the date format. If you want to modify this to dd/MM/yy, please modify it.

Reference:

• formatDate(date, timeZone, format)