如何检查值是否存在于Google电子表格中或不使用应用脚本 [英] How to check if the value exist in google spreadsheet or not using apps script

问题描述

如何使用Google Play电子表格检查该值是否存在于Google电子表格中 我想检查Sam是否存在于整个电子表格中，或者不使用apps脚本.如果存在，我要执行任务...

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);

  }


}

有什么方法可以做到吗

推荐答案

答案:

您可以定义一个textFinder并在您的数据范围内运行它.

Answer:

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

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: 
  }  
}

此代码将:

查找所有包含"Sam"的单元格在电子表格的第一张纸上
• 将包含"Sam"的Range对象附加到对象中.到一系列范围
• 将范围数组映射到A1表示法的数组，这些表示法是所有包含"Sam"的单元格.

• 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".

从这里您可以对范围进行所需的操作.如果山姆"不在工作表中，则occurrences将是一个空数组，您可以在此处执行所需的操作.

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.

希望对您有帮助！

• 类TextFinder |应用脚本Google Developers

• Class TextFinder | Apps Script | Google Developers

这篇关于如何检查值是否存在于Google电子表格中或不使用应用脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！