Searching for a value and if a value exists adding a row with copying the data in google spreadsheet

Question

I want to search for a word in a certain column and then if the value exists, I want to copy the row below with its values and change the word to two different words. My issue was in getting the found word row Number to insert a row below it.

function myFunction() {
var ss = SpreadsheetApp.openById("1fE404JUbw3aytlqtoht6FfrIhYhTpSe2MM5UDnBkFc4");
var sheet = ss.getSheets()[0]
let range = sheet.getDataRange();
var values = range.getValues();
var typeRange = sheet.getRange("E2:E");
var typeValues = typeRange.getValues();

  var i;
  for(i = 0; i <= lastRow ; i++){
    for (let type of typeValues){
      if (type == "Both"){
        var bothRow = i+1;
        
      }
////      var bothRow = sheet.getRange(i+1,1,1,typeValues[i].length);
////      ss.insertRowsAfter(bothRow, 1);
    }
  }
}

I have used alert to check and it inserted an infinite number of rows after row number 1.

function myFunction() {
  
  let sheetui= SpreadsheetApp.getUi()
  sheetui.createMenu("Rahaf Game")
  .addItem("Stage 1", 'pop')
  .addToUi();
  
//  var ss = SpreadsheetApp.openById("1fE404JUbw3aytlqtoht6FfrIhYhTpSe2MM5UDnBkFc4");
//  var sheet = ss.getSheets()[0]
//  let range = sheet.getDataRange();
//  var values = range.getValues();
//  var typeRange = sheet.getRange("E2:E");
//  var typeValues = typeRange.getValues();
}
  function pop(){
  //var ss = SpreadsheetApp.openById("1fE404JUbw3aytlqtoht6FfrIhYhTpSe2MM5UDnBkFc4");
  var sheet = ss.getSheets()[0]
  var typeRange = sheet.getRange("E2:E");
  var typeValues = typeRange.getValues();
  var lastRow = sheet.getLastRow();
  var i;
  for(i = 0; i <= lastRow ; i++){
    for (let type of typeValues){
      if (type == "Both"){
        var bothRow = i+1;
        ss.insertRowsAfter(bothRow, 1);
      }
////      var bothRow = sheet.getRange(i+1,1,1,typeValues[i].length);
////      ss.insertRowsAfter(bothRow, 1);
    }
  }
}

Can someone please help in achieving the required result in inserting a row below and copy the values into it with changing the word into two words?

What is the issue? What is happening in place of what should be happening? Any errors? — Rafa Guillermo, Apr 10 '20 at 07:50
I have noticed that it is bot getting inside the if condition! — RAHAF, Apr 10 '20 at 07:56

score 0 · Answer 1 · edited Nov 05 '21 at 09:49

0

Answer:

.getValues() returns a 2D array of values and you are referencing it as if it is unidimentional.

Code fix:

You need to change your conditional such that the object you are checking is an array:

if (type == "Both"){
//code
}

Should be:

if (type == ["Both"]){
//code
}

Adding a row and copying the data to it:

You can add a row in a sheet after a given row with the insertRowsAfter() and use .getRange().setValues() to copy in the data:

for(var i = 0; i <= lastRow ; i++){
  for (let type of typeValues){
    if (type == ["Both"]){
      var bothRow = i + 1;
      ss.insertRowsAfter(bothRow, 1);
      // increment lastRow as there is now one more row in the sheet
      lastRow++;
      // increment i as you want to skip the row you just copied!
      i++;
      var rangeToCopy = sheet.getRange(bothRow + ':' + bothRow).getValues();
      sheet.getRange((bothRow + 1) + ':' + (bothRow + 1)).setValues(rangeToCopy);
    }
  }
}

Don't forget to increment lastRow by 1 if you add a new row so that your loop still reaches the end of the sheet.

References:

Class Range | Apps Script - Method getValues()

Related Questions:

Using Google Sheets scripts, why does my if statement always return false when comparing cell values?

edited Nov 05 '21 at 09:49

Nimantha

6,405
6
28
69

answered Apr 10 '20 at 08:02

Rafa Guillermo

14,474
3
18
54

this worked for me, can you also advise regarding copying data to the last row of the rows matching the condition? – RAHAF Apr 10 '20 at 14:30
Why isn't `ss.insertRowsAfter(bothRow, 1)` suitable? Also if you're only inserting one row then `ss.insertRowAfter(bothRow)` should be enough. – Rafa Guillermo Apr 10 '20 at 14:55
You do need to add 1 to lastRow whenever you insert however else your loop won't reach the end of the sheet. – Rafa Guillermo Apr 10 '20 at 14:55
but I want to copy the data of the row that meets the condition and paste it into the new row. – RAHAF Apr 10 '20 at 14:56
I've updated my answer to include this, glad to be able to help! – Rafa Guillermo Apr 10 '20 at 15:00
I have tried the above edited code and got this Error Message: (Exception: Range not found (line 26, file "bothCondition")) – RAHAF Apr 10 '20 at 15:50
Which line is line 26? – Rafa Guillermo Apr 10 '20 at 15:58
var rangeToCopy = ss.getRange(bothRow + ':' + bothRow).getValues(); – RAHAF Apr 10 '20 at 16:17
Ahh sorry - the `.getRange()` should be of `sheet`, not `ss`. Updated my code. – Rafa Guillermo Apr 10 '20 at 16:21
Can you log the value of bothRow before the line and check it's value? – Rafa Guillermo Apr 10 '20 at 17:34
I got this: Exception: Bad value (line 26, file "bothCondition") – RAHAF Apr 10 '20 at 17:44
Have you assigned i with var in the for loop? var i = 0 rather than just i = 0? – Rafa Guillermo Apr 10 '20 at 18:00
Please can you share your sheet then; this is hard to debug without it – Rafa Guillermo Apr 10 '20 at 18:37