{"id":355,"date":"2019-09-19T22:08:15","date_gmt":"2019-09-19T13:08:15","guid":{"rendered":"http:\/\/localhost:8000\/?p=355"},"modified":"2021-01-17T22:13:13","modified_gmt":"2021-01-17T13:13:13","slug":"google-apps-script","status":"publish","type":"post","link":"http:\/\/localhost:8000\/2019\/09\/google-apps-script.html","title":{"rendered":"Google Apps Script\uff08GAS\uff09\u306eTips"},"content":{"rendered":"

Google Apps Script\uff08GAS\uff09\u306b\u95a2\u3059\u308bTips\u3092\u66f8\u304d\u305f\u3081\u3066\u3044\u304d\u307e\u3059\u3002<\/p>\n

\u5b9f\u884c\u4e2d\u306e\u95a2\u6570\u3092\u5f37\u5236\u505c\u6b62\u3059\u308b<\/h2>\n

https:\/\/script.google.com\/home\/executions<\/a>
\n\u306b\u30a2\u30af\u30bb\u30b9\u3057\u3066\u3001\u5bfe\u8c61\u306e\u5b9f\u884c\u3092\u30ad\u30e3\u30f3\u30bb\u30eb\u3057\u307e\u3059\u3002<\/p>\n

\u30ed\u30b0\u78ba\u8a8d<\/h2>\n

Logger.log()<\/code>\u3067\u51fa\u529b\u3057\u305f\u30ed\u30b0\u306fstackdriver\u306b\u51fa\u529b\u3055\u308c\u307e\u3059\u304c\u3001
\n
https:\/\/script.google.com\/home\/executions<\/a>
\n\u304b\u3089\u78ba\u8a8d\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n

\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u304b\u3089\u5024\u3092\u53d6\u5f97\u3059\u308b<\/h2>\n

1\u301c\u6700\u5927\u884c\u6570\u307e\u30672\u301c3\u5217\u76ee\u306e\u5024\u3092\u53d6\u5f97\u3059\u308b\u30b1\u30fc\u30b9\u306b\u3064\u3044\u3066\u66f8\u3044\u3066\u3042\u308a\u307e\u3059\u3002
\n\u5bfe\u8c61\u30c7\u30fc\u30bf\u304c\u591a\u3044\u5834\u5408\u306f\u3001getValues()\u3067\u4e8c\u6b21\u5143\u914d\u5217\u3068\u3057\u3066\u4e00\u62ec\u3067\u5024\u3092\u53d6\u5f97\u3059\u308b\u306e\u304c\u9244\u5247\u3067\u3059\u3002<\/p>\n

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();\nvar sheet = spreadsheet.getSheetByName('\u30b7\u30fc\u30c81');\nvar lastRownum = sheet.getLastRow();\n\n\/\/ for\u6587\u3067row\u3068column\u6307\u5b9a\u3057\u306a\u304c\u3089\u9010\u6b21\u5024\u3092\u53d6\u5f97\u3059\u308b\u30d1\u30bf\u30fc\u30f3\n\/\/ -> getValue()=1API\u547c\u3073\u51fa\u3057\u306a\u306e\u3067\u3001API\u547c\u3073\u51fa\u3057\u56de\u6570\u304c\u591a\u3059\u304e\u308b\u3068\u30b3\u30b9\u30c8\u304c\u5927\u304d\u3044\nfor (var rownum = 1; rownum <= lastRownum; rownum++) {\n  var valueRow2 = sheet.getRange(rownum, 2).getValue();\n  var valueRow3 = sheet.getRange(rownum, 3).getValue();\n}\n\n\/\/ range\u3092\u53d6\u5f97\u3057\u3066\u3001\u5024\u3092\u307e\u3068\u3081\u3066\u53d6\u5f97\u3059\u308b\u30d1\u30bf\u30fc\u30f3\n\/\/ -> range\u5185\u306e\u5024\u3092\u4e8c\u6b21\u5143\u914d\u5217\u3068\u3057\u3066\u53d6\u5f97\u3059\u308b\u3002\u5024\u306a\u3057\u3067\u3082""\u304c\u8a2d\u5b9a\u3055\u308c\u308b\u306e\u3067\u3001\u884c\u6570\u30fb\u5217\u6570\u306f\u56fa\u5b9a\u3002\u30b3\u30b9\u30c8\u306f\u5c0f\u3055\u3044\nvar values = sheet.getRange(1, 2, lastRownum, 2).getValues();\n<\/code><\/pre>\n

\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306b\u5024\u3092\u8a2d\u5b9a\u3059\u308b<\/h2>\n

1\u301c4\u884c\u76ee\u306e2\u301c3\u5217\u76ee\u306b\u5024\u3092\u8a2d\u5b9a\u3059\u308b\u30b1\u30fc\u30b9\u306b\u3064\u3044\u3066\u66f8\u3044\u3066\u3042\u308a\u307e\u3059\u3002<\/p>\n

var values = [\n  [11, 22],\n  [33, 44],\n  [55, 66],\n  [77, 88],\n];\n\n\/\/ 1\u30bb\u30eb\u305a\u3064\u8a2d\u5b9a\u3059\u308b\u30b1\u30fc\u30b9\nfor (var i = 0; i < values.length; i++) {\n  var row = values[i];\n  for (var j = 0; j < row.length; j++) {\n    var value = row[j];\n    sheet.getRange(i + 1, j + 2).setValue(value);\n  }\n}\n\n\/\/ \u307e\u3068\u3081\u3066\u8a2d\u5b9a\u3059\u308b\u30b1\u30fc\u30b9\nsheet.getRange(1, 2, 4, 2).setValues(values);<\/code><\/pre>\n

\u5916\u90e8API\u3092\u547c\u3073\u51fa\u3059<\/h2>\n

UrlFetchApp.fetch API<\/a>\u3092\u5229\u7528\u3057\u307e\u3059\u3002<\/p>\n

function main() {\n  var name = 'Taro Yamada';\n\n  var url = getGooglePatentUrl(name);\n  var options = {\n    'muteHttpExceptions': true \/\/ 503\u30a8\u30e9\u30fc\u306a\u3069\u304c\u304a\u304d\u3066\u3082\u3001\u30a8\u30e9\u30fc\u6271\u3044\u306b\u3057\u306a\u3044\n  };\n  var response = UrlFetchApp.fetch(url, options);\n\n  var responseCode = response.getResponseCode();\n  var responseBody = response.getContentText();\n  var jsonData = JSON.parse(responseBody); \/\/ \u3053\u306eAPI\u306fapplication\/json\u3092\u8fd4\u3059\u306e\u3067json\u30d1\u30fc\u30b9\u3057\u3066\u5229\u7528\u3059\u308b\n\n  Logger.log(jsonData.hoge.fuga);\n}\n\n\/\/ Google Patent\u306e\u767a\u660e\u8005\u691c\u7d22API\u306eurl\u3092\u8fd4\u3059\nfunction getGooglePatentUrl(name) {\n  normalizedName = name.replace(\/,\/g, '').replace(\/ \/g, '+');\n  query = encodeURI('inventor=' + normalizedName);\n  return 'https:\/\/patents.google.com\/xhr\/query?url=' + query;\n}\n<\/code><\/pre>\n

\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u4e0a\u306e\u30dc\u30bf\u30f3\u306b\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u5272\u308a\u5f53\u3066\u308b<\/h2>\n

\u4ee5\u4e0b\u306e\u624b\u9806\u3067\u5272\u308a\u5f53\u3066\u53ef\u80fd\u3067\u3059\u3002<\/p>\n

    \n
  1. \u30b9\u30af\u30ea\u30d7\u30c8\u3092\u8a18\u8f09\u3059\u308b<\/li>\n
  2. \u56f3\u5f62\u63cf\u753b\u3067\u30dc\u30bf\u30f3\u3092\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u4e0a\u306b\u914d\u7f6e\u3059\u308b<\/li>\n
  3. \u30dc\u30bf\u30f3\u306e\u300c\u30fb\u30fb\u30fb\u300d\u30e1\u30cb\u30e5\u30fc\u304b\u3089\u300c\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u5272\u308a\u5f53\u3066\u300d\u3092\u9078\u629e\u3057\u3066\u3001\u4e8b\u524d\u306b\u4f5c\u6210\u3057\u3066\u304a\u3044\u305f\u30b9\u30af\u30ea\u30d7\u30c8\u540d\u3092\u8a18\u5165\u3059\u308b<\/li>\n
  4. \u3042\u3068\u306f\u30dc\u30bf\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3059\u308b\u3068\u3001\u30b9\u30af\u30ea\u30d7\u30c8\u304c\u5b9f\u884c\u3055\u308c\u308b
    \n\u203b\u30c0\u30d6\u30eb\u30af\u30ea\u30c3\u30af\u3059\u308b\u3068\u56f3\u5f62\u7de8\u96c6\u753b\u9762\u304c\u8868\u793a\u3055\u308c\u307e\u3059<\/li>\n<\/ol>\n

    \u6700\u7d42\u884c\u3092\u53d6\u5f97<\/h2>\n
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();\n\n\/\/ \u30b7\u30fc\u30c8\u306e\u6700\u5927\u884c\u3092\u53d6\u5f97\nvar rownum = sheet.getMaxRows();\n\n\/\/ \u30b7\u30fc\u30c8\u306e\u4e2d\u3067\u5024\u304c\u5165\u529b\u3055\u308c\u3066\u3044\u308b\u6700\u5927\u884c\u3092\u53d6\u5f97\nvar rownum = sheet.getLastRow();\n\n\/\/ A\u5217\u304c\u7a7a\u767d\u3067\u306a\u3044\u6700\u7d42\u884c\u3092\u53d6\u5f97\uff08\u7a7a\u767d\u3058\u3083\u306a\u3044\u5024\u306e\u6570\u3092\u6570\u3048\u3066\u3044\u308b\u3002\u9014\u4e2d\u306b\u7a7a\u767d\u304c\u3042\u308b\u30b1\u30fc\u30b9\u3067\u306f\u4f7f\u3048\u306a\u3044\u3002\u3067\u3082\u9ad8\u901f\uff09\nvar rownum = sheet.getRange('A:A').getValues().filter(String).length;\n\n\/\/ A\u5217\u3067\u7a7a\u767d\u3067\u306a\u3044\u6700\u7d42\u884c\u3092\u53d6\u5f97\uff08LastRow\u304b\u3089\u9006\u306b\u30c1\u30a7\u30c3\u30af\u3057\u3066\u3044\u304f\u3002\u9014\u4e2d\u306b\u7a7a\u767d\u304c\u3042\u3063\u3066\u3082OK\u3002\u3067\u3082\u4f4e\u901f\uff09\nvar rownum;\nfor (var i = sheet.getLastRow(); i > 0; i--) {\n  if (!sheet.getRange(i, 1).isBlank()) {\n    rownum = i;\n    break;\n  }\n}\n<\/code><\/pre>\n

    V8\u30a8\u30f3\u30b8\u30f3\u3092\u6709\u52b9\u306b\u3059\u308b<\/h2>\n

    2020\u5e742\u6708\u306bGAS\u304cV8\u30a8\u30f3\u30b8\u30f3\u3092\u30b5\u30dd\u30fc\u30c8\u3059\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3057\u305f\u3002\u73fe\u5728\u3067\u306f\u65b0\u3057\u304fGAS\u306e\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u3092\u4f5c\u6210\u3059\u308b\u3068\u3001V8\u30a8\u30f3\u30b8\u30f3\u304c\u6709\u52b9\u306b\u306a\u3063\u3066\u3044\u308b\u3088\u3046\u3067\u3059\u3002
    \n\u53e4\u3044GAS\u306e\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u306e\u5834\u5408\u3067\u3082\u3001\u5b9f\u884c\u30bf\u30d6\u304b\u3089\u6709\u52b9\u306b\u3059\u308b\u3060\u3051\u3067\u7c21\u5358\u306bV8\u30a8\u30f3\u30b8\u30f3\u3092\u5229\u7528\u3067\u304d\u307e\u3059\u3002
    \n\"\"<\/p>\n

    const \u3084 let \u306e\u30b9\u30b3\u30fc\u30d7<\/h3>\n

    \u53e4\u3044GAS\u306eJavaScript\u30a8\u30f3\u30b8\u30f3\uff08V8\u30a8\u30f3\u30b8\u30f3\u3092\u7121\u52b9\u306b\u3057\u305f\u72b6\u614b\uff09<\/h4>\n

    \u5909\u6570\u5ba3\u8a00\u306b const<\/code>\u3084let<\/code>\u3082\u4f7f\u3048\u308b\u306e\u3067\u3059\u304c\u3001\u30b9\u30b3\u30fc\u30d7\u304c\u304a\u304b\u3057\u306a\u3053\u3068\u306b\u306a\u3063\u3066\u3044\u307e\u3059\u3002<\/p>\n

    function myFunction() {\n  for (var i = 1; i <= 2; i++) {\n    const constVal = i;\n    let :letVal = i;\n    Logger.log('for\u30d6\u30ed\u30c3\u30af\u306e\u4e2d: constVal: ' + constVal + ', letVal: ' + letVal);\n  }\n  Logger.log('for\u30d6\u30ed\u30c3\u30af\u306e\u5916: constVal: ' + constVal + ', letVal: ' + letVal);\n}<\/code><\/pre>\n

    \u3053\u306e\u51fa\u529b\u7d50\u679c\u306f\u4ee5\u4e0b\u3067\u3059\u3002<\/p>\n

    [19-09-27 10:16:36:725 JST] for\u30d6\u30ed\u30c3\u30af\u306e\u4e2d: constVal: 1, letVal: 1\n[19-09-27 10:16:36:726 JST] for\u30d6\u30ed\u30c3\u30af\u306e\u4e2d: constVal: 1, letVal: 2\n[19-09-27 10:16:36:727 JST] for\u30d6\u30ed\u30c3\u30af\u306e\u5916: constVal: 1, letVal: 2<\/code><\/pre>\n