Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
mBaaSのデータをGoogleスプレッドシートへダウンロード
function syncData() {
var userProperties = PropertiesService.getScriptProperties();
var application_key = "APPLICATION_KEY";
var client_key = "CLIENT_KEY";
var ncmb = NCMB.init(application_key, client_key);
var classes = [
"Sessions",
"Speakers"
];
for (var i = 0; i < classes.length; i += 1) {
syncClass(ncmb, classes[i]);
}
for (var i = 0; i < classes.length; i += 1) {
setPointer(classes[i]);
}
}
function setPointer(sheetName) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var column = 2;
while (true) {
var field = sheet.getRange(1, column).getValue();
if (field == '') break;
column += 1;
}
var rowIndex = 2;
while (true) {
// すべての行が空であれば、処理終了とします
var range = sheet.getRange(rowIndex, 1, 1, column);
if (range.isBlank()) break;
// 各行を処理します
for (var i = 0; i < column; i += 1) {
// 表示されている値と、そこで使われている計算式を取ります
var value = sheet.getRange(rowIndex, i + 1).getValue();
if (value == '') continue;
var match = value.match && value.match(/^{__type=Pointer, className=(.*?), objectId=(.*?)}$/);
if (match) {
var targetSheetName = match[1];
var targetObjectId = match[2];
var rowId = getTargetRow(targetSheetName, targetObjectId);
if (rowId) {
var formula = "=" + targetSheetName + "!A" + rowId;
Logger.log(formula);
sheet.getRange(rowIndex, i + 1).setFormula(formula);
}
}
}
rowIndex += 1;
}
}
function getTargetRow(targetSheetName, targetObjectId) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(targetSheetName);
var rowIndex = 2;
while (true) {
// すべての行が空であれば、処理終了とします
var objectId = sheet.getRange(rowIndex, 1).getValue();
if (objectId == '') break;
if (objectId == targetObjectId) {
return rowIndex;
}
rowIndex += 1;
}
return null;
}
function syncClass(ncmb, sheetName) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
if (sheet) {
ss.deleteSheet(sheet);
}
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
// クラス名の取得と、データストアの準備
var Item = ncmb.DataStore(sheetName);
// 現在クラスにある全データ(最大1000件)を取得します
try{
var items = Item.limit(1000).fetchAll();
var index = 2;
// ヘッダーをすべて取得
var headers = [];
for (var i = 0; i < items.length; i += 1) {
var row = items[i];
for (var j in row.fields) {
if (['objectId', 'updateDate', 'acl', 'createDate'].indexOf(j) > -1) continue;
headers[j] = true;
}
}
// ヘッダー作成
sheet.getRange(1, 1).setValue('objectId');
var column = 2;
for (var j in headers) {
sheet.getRange(1, column).setValue(j);
column += 1;
}
for (var i = 0; i < items.length; i += 1) {
var row = items[i];
column = 2;
sheet.getRange(index, 1).setValue(row.fields['objectId']);
for (var j in headers) {
var value = row.fields[j];
if (value) {
if (value.match && value.match(/^[0-9]*:[0-9]*$/)) {
sheet.getRange(index, column).setFormula("=\"" + value + "\"");
} else {
sheet.getRange(index, column).setValue(value);
}
}
column += 1;
}
index += 1;
}
}catch(e) {
Logger.log(e)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment