Last active
November 14, 2016 09:41
-
-
Save miyakeryo/9383548 to your computer and use it in GitHub Desktop.
Postされたデータをスプレッドシートに書き込む GoogleAppsScript
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Postされたデータをスプレッドシートに書き込む | |
* | |
* @author Miyake Ryo ( http://miyakeryo.com ) | |
* @instruction http://miyakeryo.com/?p=771 | |
*/ | |
function doPost(request) { | |
try{ | |
var ss = SpreadsheetApp.openById(ScriptProperties.getProperty('active')); | |
//確認メールのコピーを送る管理者のメールアドレス | |
var adminEmail = ss.getOwner().getEmail(); | |
//エラー発生時に管理者に送るメールの件名 | |
var errorSubject = 'エラーが発生しました'; | |
//何シート目に書き込むか | |
var dataSheetNo = 0; | |
//更新日時列名 | |
var updateAtName = 'update_at'; | |
var dataSheet = ss.getSheets()[dataSheetNo]; | |
var lastRow = dataSheet.getLastRow(); | |
var lastCol = dataSheet.getLastColumn(); | |
var headers = null; | |
if (lastRow!==0 && lastCol!==0){ | |
headers = dataSheet.getRange(1, 1, 1, lastCol).getValues()[0]; | |
}else{ | |
lastRow++; | |
} | |
var a1 = dataSheet.getRange('a1'); | |
var data = request.parameter; | |
var key, value, i, exist; | |
for ( key in data ) { | |
value = data[key]; | |
exist = false; | |
for (i in headers) { | |
if ( headers[i] == key ){ | |
a1.offset(lastRow, i).setValue(value); | |
exist = true; | |
break; | |
} | |
} | |
if (!exist){ | |
a1.offset(0, lastCol).setValue(key); | |
a1.offset(lastRow, lastCol).setValue(value); | |
lastCol++; | |
} | |
} | |
if (lastCol > 0){ | |
headers = dataSheet.getRange(1, 1, 1, lastCol).getValues()[0]; | |
var pos = headers.indexOf(updateAtName); | |
value = new Date(); | |
if (pos < 0){ | |
a1.offset(0, lastCol).setValue(updateAtName); | |
a1.offset(lastRow, lastCol).setValue(value); | |
}else{ | |
a1.offset(lastRow, pos).setValue(value); | |
} | |
} | |
var result = {result:'ok'}; | |
return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON); | |
} catch(e) { | |
MailApp.sendEmail(adminEmail, errorSubject, e); | |
var result = {result:'error',error:e}; | |
return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON); | |
} | |
} | |
function setUp() { | |
PropertiesService.getScriptProperties().setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId()); | |
} |
設置してもスプレッドシートが更新されません。(スクリプト関数が見つかりません: doPost というエラーが出ます)何シート目に書き込むか等どこか変更箇所が必要でしょうか。
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
使い方 http://miyakeryo.com/?p=771