{"id":11905,"date":"2019-07-03T12:00:04","date_gmt":"2019-07-03T03:00:04","guid":{"rendered":"https:\/\/www.ka-net.org\/blog\/?p=11905"},"modified":"2019-07-03T13:54:31","modified_gmt":"2019-07-03T04:54:31","slug":"google-apps-script%e3%82%b9%e3%83%97%e3%83%ac%e3%83%83%e3%83%89%e3%82%b7%e3%83%bc%e3%83%88%e3%81%a7%e6%a4%9c%e7%b4%a2%e3%81%a8%e7%bd%ae%e6%8f%9b%e3%82%92%e8%a1%8c%e3%81%86","status":"publish","type":"post","link":"https:\/\/www.ka-net.org\/blog\/?p=11905","title":{"rendered":"[Google Apps Script]\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3067\u691c\u7d22\u3068\u7f6e\u63db\u3092\u884c\u3046"},"content":{"rendered":"

Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8<\/a>\u306b\u306f\u3001\u691c\u7d22\u3068\u7f6e\u63db\u6a5f\u80fd(Ctrl + F)\u304c\u5099\u308f\u3063\u3066\u3044\u307e\u3059\u3002<\/p>\n

\"\"<\/a><\/p>\n

\u4eca\u5e744\u6708\u306eApps Script<\/a>\u306e\u30a2\u30c3\u30d7\u30c7\u30fc\u30c8\u306b\u3088\u3063\u3066\u3001TextFinder<\/a>\u30af\u30e9\u30b9\u304c\u5b9f\u88c5\u3055\u308c\u3001Apps Script\u304b\u3089\u3082\u7c21\u5358\u306b\u691c\u7d22\u3068\u7f6e\u63db\u3092\u884c\u3046\u3053\u3068\u304c\u3067\u304d\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3057\u305f\u3002<\/p>\n

\u3059\u3079\u3066\u306e\u30b7\u30fc\u30c8\u3092\u5bfe\u8c61\u306b\u691c\u7d22<\/h4>\n

\u300c\u691c\u7d22\u3068\u7f6e\u63db\u300d\u30c0\u30a4\u30a2\u30ed\u30b0\u306e\u300c\u691c\u7d22<\/span>\u300d\u304b\u3089\u691c\u7d22\u5bfe\u8c61\u3092\u6307\u5b9a\u3067\u304d\u308b\u3088\u3046\u306b\u3001\u30b9\u30af\u30ea\u30d7\u30c8\u304b\u3089\u3082\u691c\u7d22\u5bfe\u8c61\u3092\u6307\u5b9a\u3067\u304d\u307e\u3059\u3002<\/p>\n

\"\"<\/a><\/p>\n

\u307e\u305a\u306f\u3001\u3059\u3079\u3066\u306e\u30b7\u30fc\u30c8\u3092\u5bfe\u8c61\u306b\u691c\u7d22\u3092\u884c\u3046\u30b3\u30fc\u30c9\u3067\u3059\u3002<\/p>\n

Spreadsheet.createTextFinder<\/a>\u30e1\u30bd\u30c3\u30c9\u3067TextFinder<\/a>\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u53d6\u5f97\u3057\u3001findAll<\/a>\u30e1\u30bd\u30c3\u30c9\u3067\u691c\u7d22\u3092\u884c\u3046\u30b3\u30fc\u30c9\u3067\u3059\u3002<\/p>\n

function sample1() {\r\n  Logger.log("--- sample1 ---");\r\n  var targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();\r\n  var finder = targetSpreadsheet.createTextFinder("\u6cb3").useRegularExpression(true); \/\/[\u6b63\u898f\u8868\u73fe\u3092\u4f7f\u7528\u3057\u305f\u691c\u7d22]\u6709\u52b9\r\n  var results = finder.findAll();\r\n  results.forEach(function(rng) {\r\n    Logger.log(rng.getSheet().getName() + ", " + rng.getA1Notation() + ", " + rng.getValue());\r\n  });  \r\n}<\/pre>\n
createTextFinder\u30e1\u30bd\u30c3\u30c9\u306e\u5f15\u6570\u3067\u6307\u5b9a\u3057\u3066\u3044\u308b\u306e\u306f\u691c\u7d22\u6587\u5b57\u5217\u3067\u3001useRegularExpression<\/a>\u30e1\u30bd\u30c3\u30c9\u306e\u5f15\u6570\u3092true\u306b\u3059\u308b\u3053\u3068\u3067\u3001\u300c\u6b63\u898f\u8868\u73fe\u3092\u4f7f\u7528\u3057\u305f\u691c\u7d22<\/span>\u300d\u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u6709\u52b9\u306b\u3057\u3066\u3044\u307e\u3059(\u4eca\u56de\u306e\u4f8b\u3067\u306f\u6b63\u898f\u8868\u73fe\u3092\u4f7f\u7528\u3057\u3066\u3044\u307e\u305b\u3093\u304c\u30fb\u30fb\u30fb)\u3002<\/p>\n
\u7d50\u679c\u306f\u4e0b\u56f3\u306e\u901a\u308a\u3067\u3001\u3059\u3079\u3066\u306e\u30b7\u30fc\u30c8\u4e0a\u3067\u691c\u7d22\u304c\u884c\u308f\u308c\u3066\u3044\u308b\u306e\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n
\"\"<\/a><\/p>\n
\u306a\u304a\u3001\u4eca\u56de\u306e\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u30c6\u30b9\u30c8\u3059\u308b\u306b\u3042\u305f\u3063\u3066\u3001\u30c7\u30fc\u30bf\u306f\u300c\u306a\u3093\u3061\u3083\u3063\u3066\u500b\u4eba\u60c5\u5831<\/a>\u300d\u3067\u751f\u6210\u3057\u305f\u3082\u306e\u3092\u5229\u7528\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
\u6307\u5b9a\u3057\u305f\u30b7\u30fc\u30c8\u3092\u5bfe\u8c61\u306b\u691c\u7d22<\/h4>\n
\u6b21\u306f\u3001\u30b7\u30fc\u30c8\u3092\u6307\u5b9a\u3057\u3066\u691c\u7d22\u3092\u884c\u3046\u30b3\u30fc\u30c9\u3067\u3059\u3002
\nSheet.createTextFinder<\/a>\u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u7528\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
function sample2() {\r\n  Logger.log("--- sample2 ---");\r\n  var targetSheet = SpreadsheetApp.getActiveSheet();\r\n  var finder = targetSheet.createTextFinder("\u6cb3").useRegularExpression(true); \/\/[\u6b63\u898f\u8868\u73fe\u3092\u4f7f\u7528\u3057\u305f\u691c\u7d22]\u6709\u52b9\r\n  var results = finder.findAll();\r\n  results.forEach(function(rng) {\r\n    Logger.log(rng.getSheet().getName() + ", " + rng.getA1Notation() + ", " + rng.getValue());\r\n  });\r\n}<\/pre>\n
\"\"<\/a><\/p>\n
\u6307\u5b9a\u3057\u305f\u30bb\u30eb\u7bc4\u56f2\u3092\u5bfe\u8c61\u306b\u691c\u7d22<\/h4>\n
\u6b21\u306f\u3001\u6307\u5b9a\u3057\u305f\u30bb\u30eb\u7bc4\u56f2\u3092\u5bfe\u8c61\u306b\u691c\u7d22\u3092\u884c\u3046\u30b3\u30fc\u30c9\u3067\u3059\u3002
\nRange.createTextFinder<\/a>\u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u7528\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
function sample3() {\r\n  Logger.log("--- sample3 ---");\r\n  var targetRange = SpreadsheetApp.getActiveSheet().getRange("A2:A20");\r\n  targetRange.activate();\r\n  var finder = targetRange.createTextFinder("\u6cb3").useRegularExpression(true); \/\/[\u6b63\u898f\u8868\u73fe\u3092\u4f7f\u7528\u3057\u305f\u691c\u7d22]\u6709\u52b9\r\n  var results = finder.findAll();\r\n  results.forEach(function(rng) {\r\n    Logger.log(rng.getSheet().getName() + ", " + rng.getA1Notation() + ", " + rng.getValue());\r\n  });\r\n}<\/pre>\n
\"\"<\/a><\/p>\n
\u30d2\u30c3\u30c8\u3057\u305f\u30bb\u30eb\u3092\u9806\u6b21\u51e6\u7406<\/h4>\n
\u6b21\u306f\u3001findNext<\/a>\u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3063\u3066\u3001\u30d2\u30c3\u30c8\u3057\u305f\u30bb\u30eb\u306b\u5bfe\u3057\u3066\u9806\u6b21\u51e6\u7406\u3092\u884c\u3046\u30b3\u30fc\u30c9\u3067\u3059\u3002<\/p>\n
function sample4() {\r\n  var ui = SpreadsheetApp.getUi();\r\n  var targetRange = SpreadsheetApp.getActiveSheet().getRange("A2:A20");\r\n  var finder = targetRange.createTextFinder("\u6cb3").useRegularExpression(true); \/\/[\u6b63\u898f\u8868\u73fe\u3092\u4f7f\u7528\u3057\u305f\u691c\u7d22]\u6709\u52b9\r\n  var res = finder.findNext();\r\n  while(res !== null) {\r\n    res.activate();\r\n    if(ui.alert("\u30bb\u30eb[ " + res.getA1Notation() + " ]\u9078\u629e\u4e2d\\n\u51e6\u7406\u3092\u4e2d\u6b62\u3057\u307e\u3059\u304b\uff1f", ui.ButtonSet.YES_NO) == ui.Button.YES) {\r\n      break;\r\n    }\r\n    res = finder.findNext();\r\n  }\r\n}<\/pre>\n
\"\"<\/a><\/p>\n
\u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u6307\u5b9a\u3057\u3066\u691c\u7d22<\/h4>\n
\u6b21\u306f\u3001\u300c\u5927\u6587\u5b57\u3068\u5c0f\u6587\u5b57\u306e\u533a\u5225\u300d\u300c\u5b8c\u5168\u306b\u4e00\u81f4\u3059\u308b\u30bb\u30eb\u3092\u691c\u7d22\u300d\u3068\u3044\u3063\u305f\u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u6307\u5b9a\u3057\u3066\u691c\u7d22\u3092\u884c\u3046\u30b3\u30fc\u30c9\u3067\u3059\u3002<\/p>\n
\u691c\u7d22\u30aa\u30d7\u30b7\u30e7\u30f3\u306e\u6307\u5b9a\u306b\u306f\u3001ignoreDiacritics<\/a>\u30e1\u30bd\u30c3\u30c9(\u767a\u97f3\u533a\u5225\u7b26\u53f7\u3092\u7121\u8996\u3059\u308b\u304b\u3069\u3046\u304b)\u3001matchCase<\/a>\u30e1\u30bd\u30c3\u30c9(\u5927\u6587\u5b57\u3068\u5c0f\u6587\u5b57\u306e\u533a\u5225)\u3001matchEntireCell<\/a>\u30e1\u30bd\u30c3\u30c9(\u5b8c\u5168\u306b\u4e00\u81f4\u3059\u308b\u30bb\u30eb\u3092\u691c\u7d22)\u3001matchFormulaText<\/a>\u30e1\u30bd\u30c3\u30c9(\u6570\u5f0f\u5185\u3082\u691c\u7d22)\u3001useRegularExpression<\/a>\u30e1\u30bd\u30c3\u30c9(\u6b63\u898f\u8868\u73fe\u3092\u4f7f\u7528\u3057\u305f\u691c\u7d22)\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n
\/\/\u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u6307\u5b9a\u3057\u3066\u691c\u7d22\r\nfunction sample5() {\r\n  Logger.log("--- sample5 ---");\r\n  var targetSheet = SpreadsheetApp.getActiveSheet();\r\n  var finder = targetSheet.createTextFinder("IKEUCHI_hiroshi@example.com")\r\n    .ignoreDiacritics(true)  \/\/\u767a\u97f3\u533a\u5225\u7b26\u53f7\u3092\u7121\u8996\u3059\u308b\r\n    .matchCase(true)         \/\/[\u5927\u6587\u5b57\u3068\u5c0f\u6587\u5b57\u306e\u533a\u5225]\u6709\u52b9\r\n    .matchEntireCell(true)   \/\/[\u5b8c\u5168\u306b\u4e00\u81f4\u3059\u308b\u30bb\u30eb\u3092\u691c\u7d22]\u6709\u52b9\r\n    .matchFormulaText(true); \/\/[\u6570\u5f0f\u5185\u3082\u691c\u7d22]\u6709\u52b9\r\n  var results = finder.findAll();\r\n  results.forEach(function(rng) {\r\n    Logger.log(rng.getSheet().getName() + ", " + rng.getA1Notation() + ", " + rng.getValue());\r\n  });\r\n}<\/pre>\n
\"\"<\/a><\/p>\n
\u30d2\u30c3\u30c8\u3057\u305f\u6587\u5b57\u5217\u3092\u4e00\u62ec\u7f6e\u63db<\/h4>\n
\u6b21\u306f\u3001replaceAllWith<\/a>\u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3063\u3066\u3001\u30d2\u30c3\u30c8\u3057\u305f\u6587\u5b57\u5217\u3092\u4e00\u62ec\u3067\u7f6e\u63db\u3059\u308b\u30b3\u30fc\u30c9\u3067\u3059\u3002<\/p>\n
function sample6() {\r\n  var targetSheet = SpreadsheetApp.getActiveSheet();\r\n  var finder = targetSheet.createTextFinder("@example.com");\r\n  var cnt = finder.replaceAllWith("@exampleXYZ.com");\r\n  SpreadsheetApp.getUi().alert(cnt + "\u4ef6\u7f6e\u63db\u3057\u307e\u3057\u305f\u3002");\r\n}<\/pre>\n
\"\"<\/a><\/p>\n
\u78ba\u8a8d\u3057\u306a\u304c\u3089\u9806\u6b21\u7f6e\u63db<\/h4>\n
\u6700\u5f8c\u306f\u3001findNext<\/a>\u30e1\u30bd\u30c3\u30c9\u3068replaceWith<\/a>\u30e1\u30bd\u30c3\u30c9\u3092\u4f7f\u3063\u3066\u3001\u9806\u6b21\u78ba\u8a8d\u3057\u306a\u304c\u3089\u7f6e\u63db\u3092\u884c\u3046\u30b3\u30fc\u30c9\u3067\u3059\u3002<\/p>\n
function sample7() {\r\n  var ui = SpreadsheetApp.getUi();\r\n  var targetSheet = SpreadsheetApp.getActiveSheet();\r\n  var finder = targetSheet.createTextFinder("@example.com");\r\n  var res = finder.findNext();\r\n  while(res !== null) {\r\n    switch(ui.alert("\u30bb\u30eb[ " + res.getA1Notation() + " ]\u306e\u5024[ " + res.getValue() + " ]\u3092\u7f6e\u63db\u3057\u307e\u3059\u304b\uff1f", ui.ButtonSet.YES_NO_CANCEL)) {\r\n      case ui.Button.YES:\r\n        finder.replaceWith("@exampleXYZ.com");\r\n        break;\r\n      case ui.Button.CANCEL:\r\n      case ui.Button.CLOSE:\r\n        return;\r\n      default:\r\n        break;\r\n    }\r\n    res = finder.findNext();\r\n  }\r\n}<\/pre>\n
\"\"<\/a><\/p>\n
\n
\n