CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
Marsad
Marsad

Reputation: 941

How to check if the value exist in google spreadsheet or not using apps script

How to check if the value is exist in google spreadsheet or not using apps script I want to check if the Sam exist in the entire spreadsheet or not using apps script. If exist I want to perform task...

Here is the spreadsheet

function doGet(e) {
  return HtmlService.createHtmlOutput("Hi there");
}

function doPost(e) {
  // this is where telegram works
  var data = JSON.parse(e.postData.contents);
  var text = data.message.text;
  var id = data.message.chat.id;

  var userName  = data.message.from.username;

  if(/^#/.test(text)) {
    var sheetName = text.slice(1).split(" ")[0];
    var sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName) ? SpreadsheetApp.openById(ssId).getSheetByName(sheetName) : SpreadsheetApp.openById(ssId).insertSheet(sheetName);
    var comment = text.split(" ").slice(1).join(" ");
    sheet.appendRow([userName,new Date(),id,name,comment,answer]);
  }

  //check if user is new in group
  // this gets the range
  var range = SpreadsheetApp.getActiveRange().getValues();  

  var searchString = "marsad01";

  var isSearchStringInRange = range.some( function(row){
    return row[0] === searchString

  });


  if(isSearchStringInRange){
  // do something
        sendMessage(id, answer, name);

  }else{

    sendGreetingMessage(id, answer, name);

  }


}

is there any way how to do this

Upvotes: 0

Views: 10652

Answers (2)

Rafa Guillermo
Rafa Guillermo

Reputation: 15375

Answer:

You can define a textFinder and run it over your data range.

Code:

function findSam() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];  
  var range = sheet.getDataRange();
  var textFinder = range.createTextFinder('Sam');
  var locations = [];
  
  var occurrences = textFinder.findAll().map(x => x.getA1Notation());
  
  if (occurrences == []) {
    // do something if "Sam" not in sheet 
  }
  else {
    // do stuff with each range: 
  }  
}

This code will:

  • Find all cells that contain "Sam" in the first sheet of the Spreadsheet
  • Append the Range object that contains "Sam" to an array of ranges
  • Map the array of ranges to an array of A1 notations which are all the cells which contain "Sam".

From here you can do what you wish with the ranges. If "Sam" is not in the sheet then occurrences will be an empty array and you can do here what you wish.

References:

Upvotes: 3

Neven Subotic
Neven Subotic

Reputation: 1429

Depending on if you want to select the range or just always use the whole A:A column. In the former case, do this:

// this gets the range
var range = SpreadsheetApp.getActiveRange().getValues();

// this is what you are searching for
var searchString = "Sam";

// this is whether what you are searching for exists 
var isSearchStringInRange = range.some( function(row){
    return row[0] === searchString
});

// then you can proceed to do something like
if( isSearchStringInRange ){
  // do something
}

Upvotes: 4

Related Questions