Concatenating merged cells and on a specific condition in apps script-CodePudding

I have this table where I want to concatenate each row in it on specific columns

The concatenation should happen on columns Option Name, Type 1, Type 2and Existing measure/New measure

For Existing measure and New measure wherever there is an entry that value should be picked up for concatenation.

The output I want is an array as below

[Name 1-C type 1-Yearly-GB, Name 1-C type 2-Monthly-MB, Name 2-C type 3-Quarterly-GB, Name 2-C type 2-Daily-Bytes,Name 2-C type 4-Monthly- MB,Name 3-C type 1-Yearly-KB]

I could use an array formula and concatenate and store in column L and read that column via apps script. but I want to use apps script to get the array I desire.

Please help!

CodePudding user response：

I believe your goal is as follows.

You want to convert your sample input Spreadsheet image to the array of The output I want is an array as below.
You want to put the created array in the column "L".
You want to achieve this using Google Apps Script.

In your situation, how about the following sample script?

Sample script:

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("C7:K"   sheet.getLastRow());
  let temp = "";
  const ar = range.getDisplayValues().map(([c, , , , , ...v]) => {
    if (c) temp = c;
    return [[c, ...v].join("") && [temp, ...v.filter(String)].join("-")];
  });
  console.log(ar); // You can see the created array in the log.
  // If you want to retrieve the flattened array like your question, please use console.log(ar.flat());

  range.offset(0, 9, ar.length, 1).setValues(ar);
}

When this script is run, the values are retrieved from columns "C" and "H" - "K", and an array is created including the string values concatenated columns "H" - "K". And, the created array is put to column "L".

Reference:

map()