Skip to content

Instantly share code, notes, and snippets.

@miyakeryo
Last active November 14, 2016 09:41
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save miyakeryo/9383548 to your computer and use it in GitHub Desktop.
Save miyakeryo/9383548 to your computer and use it in GitHub Desktop.
Postされたデータをスプレッドシートに書き込む GoogleAppsScript
/**
* 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());
}
@miyakeryo
Copy link
Author

@2co5li
Copy link

2co5li commented Nov 11, 2016

設置してもスプレッドシートが更新されません。(スクリプト関数が見つかりません: doPost というエラーが出ます)何シート目に書き込むか等どこか変更箇所が必要でしょうか。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment