Google Apps Script to search a string in Google Sheet [Part II]

In our last post we have seen how we can use Google Apps Script to search a string in Google Sheet and add n number of rows just after the row in which the string was found. But there is one issues : which is that it won’t loop over all the items that matches the searched string.

Here in this post i will share the code which will search the whole sheet and saves all the indexes and loop over on one by one and add the new rows.

Google Apps Script to search a string in a column in Google Sheet:

function StringSearch(){
  //To select the active spreasheet and the active sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //Getting all the values in the above sheet
  var data = sheet.getDataRange().getValues();
  //Getting the value from K4 cell which w e need to search in the sheet
  var Stringtosearch = sheet.getRange("K4").getValue();
  //Initiated an emptry array 
  var index = [];
  //Then looping over all the length of the data values
  for(var i = 1; i<data.length;i++){
    //Matching all the values in the data to the K4 cell value
    if(data[i][3] == Stringtosearch){
      //Now, pushing the matched index in array named index with an incremet of 2 as the original index starts from 0.
      index.push(i+2)
      }
    }
  //now looping over all the array 
  for (j in index){
    //multiplying the indexes with 12 as when we add the first twelve rows the index for the next rows will change with 12
    k= j * 12
    //now saving the new index in x
    x= index[j] + k
    //inserting a new rows for all the indexes.
    sheet.insertRows(x,12)
  }
  }

The above code will add new rows whenever it will find the particular string in the sheet or in the particular column in which we are searching. In our case the code will add 12 rows just after the search term.

To get more detailed knowledge about Google Apps Script, you can check the official website. Also if you want to explore more codes/posts related to Google Apps Script on our website, then you can find it here.

If you have any questions or if you need any help please get in touch with me using the comment section below.