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

Marsad
May 9, 2020
175 views
0 votes
2 Answers

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…

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

Answers

- NevenSubotic
- May 9, 2020 at 1:33 pm
- 0 votes
0
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
}
```
Login or Signup to reply.

- Ihopethisishelpfultoyou
- May 11, 2020 at 12:42 pm
- 0 votes
0
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:
- Class TextFinder | Apps Script | Google Developers
Login or Signup to reply.