I have a sheet which one column (column 10) is a drop down list with 2 possibilities : official or non official. I'm trying to create a script that export this sheet to another with this condition : Each row with "Official" in the column 10 have to be duplicated as "non official" (so in this case we will have 2 rows one official and one non official)

So this is my code :

const tab = active_sheet.getRange("A2:M14").getValues();
for (let nbline=0; nbline <tab.length;nbline  ) {
if (tab[nbline][10] == "official") {

And I don't find the command to duplicate this line, keep all information and just change the column 10 to "non official"

For exemple this is "tab" :

row 1 : a b c d e official f g h

row 2 : 1 2 3 4 5 non official 6 7 8

row 3 : x c v b x official m l k

row 4 : n j i o k non official 6 9 8

I want to have this :

row 1 : a b c d e official f g h

row 2 : a b c d e non official f g h

row 3 : 1 2 3 4 5 non official 6 7 8

row 4 : x c v b x official m l k

row 5 : x c v b x non official m l k

row 6 : n j i o k non official 6 9 8

If someone can help me

Thanks in advance !

CodePudding user response：

Script:

function myFunction() {
  const active_sheet = SpreadsheetApp.getActiveSheet();
  const tab = active_sheet.getRange("A2:M14").getValues();
  var output = [];
  // if we are checking the 10th column, column should be 9 instead (0-index), 
  // modify if needed.
  var column = 10;
  
  tab.forEach(row => {
    output.push(row);
    if(row[column] == "official")
      // directly modifying row[10] and pushing row will result
      // to the column having "non official" to all rows
      output.push([...row.slice(0, column), "non official", ...row.slice(column   1)]);
  })

  active_sheet.getRange(2, 1, output.length, output[0].length).setValues(output)
}

Sample:

Output:

Note:

• You mentioned column 10 but used [10] in your code which is the 11th column. I directly modified your script so I used 10 but feel free to modify the column to 9 if you really want the 10th column.
• There is some unexpected issue when directly changing row[column] to "non official" and pushing the same row or a variable that was equated to row. It duplicates the rows properly but the column values are all "non official", so I created another variable row2 containing the same elements and then modifying it to add as the duplicate "non official" row.
• If there are rows beyond the 14th row, it will be overwritten if there were duplicated rows. Include all the rows in the tab range to avoid it from happening.