Search code examples
sqlgoogle-apps-scriptgoogle-sheets

Google Apps Script to Transform Data

I’ve amended the question as requested.

An import range brings in the table below on to tab 1 of a google sheet:

Area Division Name Dial Email Cell
01 Bike Jason Todd 01234567894 [email protected] 01234567894
10,12,14 Assistant - Car Jason Todd 01234567894 [email protected] 01234567894
Assistant - Boat Sam Jones 01234567894 [email protected] 01234567894
92,94 Assistant - Plane Kelly Smart 01234567894 [email protected] 01234567894
Sales - Bike/Car/Boat/Plane
01 Assistant - Bike Terry Allen 01234567894 [email protected] 01234567894
01 Bike Laura Green 01234567894 [email protected] 01234567894

I want to use apps script to move the table above to tab 2 everyday at 7am and convert it to the format below:

Code Division Name Dial Email Cell Code Division Name Dial Email Cell Code Division Name Dial Email Cell
01 Category Assistant - Bike Terry Allen 01234567894 [email protected] 01234567894 01 Bike Jason Todd 01234567894 [email protected] 01234567894 01 Bike Laura Green 01234567894 [email protected] 01234567894
10 Category Assistant - Car Jason Todd 01234567894 [email protected] 01234567894
12 Category Assistant - Car Jason Todd 01234567894 [email protected] 01234567894
14 Category Assistant - Car Jason Todd 01234567894 [email protected] 01234567894
92 Category Assistant - Plane Kelly Smart 01234567894 [email protected] 01234567894
94 Category Assistant - Plane Kelly Smart 01234567894 [email protected] 01234567894

Below are the 3 main changes required when the data moves from tab 1 (input) to tab 2 (output):

  1. Any Code with multiple Codes i.e 10,12,14 need the code split by the comma (",") onto separate lines and then copy the rest of the columns next to each, see example of 10,12,14 on tab 3.

  2. Ignore any blank Code.

  3. If a code appears more than once, move it side by side to the similar code, do that for every additional similar code, give preference to the division with Assistant in it, put that first, then all the others side by side - for example 01 has 3 separate entries, they now appear side by side in tab 3 (i.e the format of the table is copied).

This is just sample data, actual data is 100s of rows.

So far I’ve been using the apps script below but it isn’t producing the right results, any idea how I can do this. Thanks

Current script that doesn’t work:

function transforminput() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inputSheet = ss.getSheetByName('Input');
var outputSheet = ss.getSheetByName('Output');
var inputData = inputSheet.getDataRange().getValues();
var outputData = [];
inputData.forEach(function(row) {
    var codes = row[0].toString().split(',').filter(Boolean);
    if (codes.length > 0) {
        var baseRow = row.slice(0, 2).concat(row.slice(2, 5));
        codes.forEach(function(code) {
            outputData.push(baseRow.concat(code, row.slice(5)));
        });
    }
});
outputSheet.clear();
outputSheet.getRange(1, 1, outputData.length, 
outputData[0].length).setValues(outputData);
}

Additional Request 9.25.23

If I put the table below as the input:

Input Table:

Area Division Name Dial Email Cell
01 Bike Jason Todd 01234567894 [email protected] 01234567894
10,12,14 Assistant - Car Jason Todd 01234567894 [email protected] 01234567894
Assistant - Boat Sam Jones 01234567894 [email protected] 01234567894
92,94 Assistant - Plane Kelly Smart 01234567894 [email protected] 01234567894
Sales - Bike/Car/Boat/Plane
01 Assistant - Bike Terry Allen 01234567894 [email protected] 01234567894
01 Bike Laura Green 01234567894 [email protected] 01234567894

I want this as the output:

Output table:

Code Division Name Dial Email Cell Code Division Name Dial Email Cell Code Division Name Dial Email Cell
01 Category Assistant - Bike Terry Allen 07896 541236 [email protected] 07896 541236 01 Bike Jason Todd 07896 541236 [email protected] 07896 541236 01 Bike Laura Green 07896 541236 [email protected] 07896 541236
10 Category Assistant - Car Jason Todd 07896 541236 [email protected] 07896 541236
12 Category Assistant - Car Jason Todd 07896 541236 [email protected] 07896 541236
14 Category Assistant - Car Jason Todd 07896 541236 [email protected] 07896 541236
92 Category Assistant - Plane Kelly Smart 07896 541236 [email protected] 07896 541236
94 Category Assistant - Plane Kelly Smart 07896 541236 [email protected] 07896 541236

Additional request 1:

I want any non-numeric values like ‘Sales - Bike/Car/Boat/Plane’ in column A (Code) removed in output.

Additional Request 2:

Row 1, Terry Allen comes first because he has ‘Assistant’ in column 2 (Division), put any ‘Assistant’ firsts.

Thanks.

Solution

  • Although I'm not sure whether I could correctly understand your question, how about the following sample script?

    Sample script:

    function myFunction() {
  const srcSheetName = "Input"; // This sheet name is from your showing script.
  const dstSheetName = "Output"; // This sheet name is from your showing script.

  // Retrieve Sheets objects.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));

  // Retrieve values from source sheet.
  const [srcHeader, ...srcValues] = srcSheet.getDataRange().getDisplayValues();

  // Create an object.
  let dstValues = [...srcValues.reduce((m, [a, ...v]) => {
    if (a) {
      a.toString().split(",").forEach(e => {
        const k = e.trim();
        const vv = [k, ...v];
        m.set(k, m.has(k) ? [...m.get(k), ...vv] : vv);
      });
    }
    return m;
  }, new Map()).values()];

  // Create an array.
  if (dstValues.length == 0) return;
  const maxLen = Math.max(...dstValues.map(r => r.length));
  srcHeader[0] = "Code";
  dstHeader = [...Array(maxLen / srcHeader.length)].flatMap(_ => srcHeader);
  dstValues = [dstHeader, ...dstValues.map(r => [...r, ...Array(maxLen - r.length).fill(null)])];

  // Put an array into the destination sheet.
  dstSheet.clear().getRange(1, 1, dstValues.length, dstValues[0].length).setNumberFormat("@").setValues(dstValues);
}

    Testing:

    When this sample script is used to your showing a sample input table, the following result is obtained.

    enter image description here

    References: