Insert row below based on the column value using google scripts

Question

I stuck with this. Seems easy at first then I got lost!

What I am aiming for:

to insert a row(s) based on a cell value (Col C) -- insert row(s) below
after a new row(s) is inserted, copy the data from previous row + string value from previous row, removing that value as well from the previous row.

I am trying to use this formula but I got lost...

function addRows(){
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheets()[1];
  var dataRange = sheet1.getDataRange();
  var dataValues = dataRange.getValues();

  for (var i = 0; i<dataValues.length; i++) {
    for (var j= 0; j<dataValues.length; i++) {
      /* If blank or 0 - zero, skip */
      if (dataValues[i][3] == "" || dataValues[i][3]== 0) {continue;}
      /* If value is >=1, insert new row(s) below the active row */
      if (dataValues[i][3] >=1) { 
      sheet1.insertRowAfter(i);
      sheet1.getRange(...) // copy the data from previous + string  
      }
    }
  }
}

In order to help to think of your goal, can you provide the sample Spreadsheet including the input and output you expect? — Tanaike
– Tanaike, Commented Feb 7, 2020 at 7:07
Here's a sample sheet - sheet6. I also updated the picture above, the "Datas" column had an incorrect text. docs.google.com/spreadsheets/d/… — user7935276
– user7935276, Commented Feb 7, 2020 at 7:14
Thank you for replying and providing the sample Spreadsheet. Where can I see the input and output you expect? You want to convert "A2:B2" to "A6:B11" in "Sheet6"? — Tanaike
– Tanaike, Commented Feb 7, 2020 at 7:18
Thank you for replying. I proposed a sample script as an answer. Could you please confirm it? If I misunderstood your question and that was not the direction you want, I apologize. — Tanaike
– Tanaike, Commented Feb 7, 2020 at 8:17

Tanaike · Accepted Answer · 2020-02-07 08:17:33Z

1

In your shared Spreadsheet, you want to convert from the values of "A2:B3" to the values of "A6:B11".

In your shared image, you want to achieve as follows.

From

Data1   A0HD, B0DP
Data2   C12X, D0B1, E2C1, F6H1

To

Data1   A0HD
Data1   B0DP
Data2   C12X
Data2   D0B1
Data2   E2C1
Data2   F6H1

You want to achieve this using Google Apps Script.

I could understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Sample script:

In order to test the script, please use the following script to your shared Spreadsheet. And run the script.

function myFunction() {
  var sourceSheetName = "Sheet6";
  var destinationSheetName = "Sheet6";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sourceSheetName);

  // Retrieve source values.
  var values = sheet.getRange("A2:B3").getValues();

  // Convert values.
  var result = values.reduce(function(ar, [a, b]) {
    var temp = b.split(",");
    for (var i = 0; i < temp.length; i++) {
      ar.push([a, temp[i].trim()]);
    }
    return ar;
  }, []);

  // Put values.
  var dstSheet = ss.getSheetByName(destinationSheetName);
  dstSheet.getRange(dstSheet.getLastRow() + 1, 1, result.length, result[0].length).setValues(result);
}

In above script, when the script is run, the values of "A2:B3" from the "Sheet6" are retrieved and the converted values are put to the last row of "Sheet6".
If only input values are put to the source sheet, you can also use var values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues(); instead of var values = sheet.getRange("A2:B3").getValues();.

Note:

This is a simple sample script. So please modify this for your actual Spreadsheet.

Reference:

reduce()

answered Feb 7, 2020 at 8:17

Tanaike

204k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user7935276 Over a year ago

Thank you so much. Yes, your script worked. Quick question, what does "ar' mean on this "function(ar, [a, b]"? I wanted to ensure I understand what it is and how it is being used.

Tanaike Over a year ago

@JMR Thank you for replying. I'm glad your issue was resolved. About your additional question, ar and [a, b] are Accumulator and Current Value of reduce, respectively. And Destructuring assignment is used to "Current Value".

Collectives™ on Stack Overflow

Insert row below based on the column value using google scripts

1 Answer 1

Sample script:

Note:

Reference:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Note:

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related