Skip to content

Instantly share code, notes, and snippets.

@miyakeryo miyakeryo/google_apps.js
Last active Nov 14, 2016

Embed
What would you like to do?
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

This comment has been minimized.

Copy link
Owner Author

miyakeryo commented Mar 9, 2014

@2co5li

This comment has been minimized.

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
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.