Excel VBA>Javascript: Loop through rows inserting rows where cell values differ

Question

I'm struggling to get javascript to run in Googlesheets to do the following:

Loop through column A:A comparing the above cell value to the below cell value
Where, for example say A2 isn't the same as A3 then insert two rows beneath it
Copy the last value above the first blank row and offset down one row into the first new inserted blank row and paste it.

Now I managed to find this example of something similar to what I'm trying to do at the basic level (hence my question here): Insert row between different data and it's from that that I got this:

function doSomething() {
 var spreadsheet = SpreadsheetApp.openById('mySheetID'),
     tab = spreadsheet.getSheets()[0], // Get the first tab
     values = tab.getRange(2, 1, tab.getLastRow(), 3).getDisplayValues(),     //Get the values beginning in the second row because of the headers
     newValues = [], // Empty array where we're gonna push the new values
     lastName;
    // Loop through all the values
 for(var i = 0; i <values.length; i++){
   // If the last name is different from the current name or the last date is different from the current date add an empty "row" to the new array
   if((lastName != undefined) && (values[i][1] != lastName)){
     newValues.push(['','','']);
   }
   //Add the current row to the new array
   newValues.push(values[i]);
   //Sets the lastDate and lastName to the current values for the next evaluation
   lastName = values[i][1];
 }
 //Sets the new values
 tab.getRange(2,1,newValues.length,3).setValues(newValues)

},

This does insert a new row but at every other row so I suspect that it's picking up on data that's in Column C (which is different on every row) as opposed to the data in Column A. I've Googled a lot to find out even how to open a sheet with the SpreadsheetApp.openById bit and so I'm asking for help or if you can point out bits of the above code to look into. I know it's been very helpfully commented but as I said I'm completely new to javascript.

If it helps there is a an Excel/VBA script that works perfectly:

Sub InsertRowsAtValueChange()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("A" & i - 1).Value <> Range("A" & i).Value Then
            Rows(i).Resize(2).Insert
            Range("M" & i).Value = Range("A" & i - 1).Value
        End If
    Next
    Application.ScreenUpdating = True
End Sub

They're some diagrams in the below VBA question but can't see how to do that here, sorry: https://www.mrexcel.com/forum/excel-questions/1034334-insert-blank-row-offset-copy-paste.html#post4964822

Thanks for your time and any help, I have no experience with javascript, sorry.

Max.

QUICK UPDATE 09/12/17:

I've managed to get parts one and two off of my "Wish List" completed now. I don't fully understand it but I've commented where I can to remind myself later:

function importFromSDE() {
  
// Open the Google Sheet
 var spreadsheet = SpreadsheetApp.openById('mySheetID'),
     
// Get the tab by name
     tab = spreadsheet.getSheetByName('Testing'), 
 
// Get the values beginning in the second row because of the headers     
// Values from Row 2, Column 1, Down to last row, across 11 columns
     values = tab.getRange(2,1,tab.getLastRow(),11).getDisplayValues(),
     
// Empty array where we're gonna push the new values       
     newValues = [],
     lastT1Item;

// Loop through all the values
  for(var i=0; i<values.length; i++){

// If the last name is different from the current name or the last date is different from the current date add an empty "row" to the new array
   if((lastT1Item != undefined) && (values[i][0] != lastT1Item)){
     newValues.push(['','','','','','','','','','','']);
      newValues.push(['','','','','','','','','','','']);
   }

//Add the current row to the new array
   newValues.push(values[i]);

//Sets the lastT1Item to the current values for the next evaluation
   lastT1Item = values[i][0];
    
 }
  
//Sets the new values
 tab.getRange(2,1,newValues.length,11).setValues(newValues)

}

Excel VBA>Javascript: Loop through rows inserting rows where cell values differ

0 Answers0