mBaaSのデータをGoogleスプレッドシートへダウンロード

index.js

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)
  }
}