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

Searching a string in a google sheet is easy when you search it manually using Ctrl+F and enter the string. But what if you want to automate the task and search the string to perform dependent task, the Google Apps Script is there for your rescue.

I came across a situation where i have to search a string in a column and add extra rows just after the row in which we find the string.

Below code will find the string for the first time we found and add the blank rows just after the row containing the string :

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

function SearchString(){
  //To select the active spreasheet and the active sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //Getting all the values in the above sheet
  var GetAllData = sheet.getDataRange().getValues();
  //Getting the value from K4 cell which we need to search in the sheet (in the above image its 201903)
  var StringtoSearch = sheet.getRange("K4").getValue();
  //Then loop over all the length of the data values
  for(var i = 0; i<GetAllData.length;i++){
    //Matching all the values in the data to the K4 cell value
    //Here GetAllData[i][1] where [i] is for row and [1] is for column in which we are searching
    if(GetAllData[i][1] == StringtoSearch){ 
      //Getting the matching value and adding the index to 1 to get the correct index as i started from 0.
      var row = i+1
      //Inserting the rows after the row
      sheet.insertRowsAfter(row, 12)
      //returning back the i
      return i
    }
  }
}

 

The above code i have shared will search that particular string in the sheet and the respected row in which the string it found, and will add the 12 rows just after the row in which the string was found.

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.