Convert Google Spreadsheet into Kinvey Collections

googleSpreadsheetToKinvey.js

function getKinveyParameters(){
  return {
    kinvey_app_id: " YOUR APP ID ",
    kinvey_master_secret: " YOUR MASTER SECRET "
  };
};

// Use this function for time based script updating, since
// the id is hard-coded
function readRowsHelper(){
  var id = " YOUR GOOGLE SPREADSHEET ID ";
  readRowsFromSpreadsheetId(id);
}

// Use this function for handling the active spreadsheet.
function readRowsFromActiveSpreadsheet(){
  readRowsFromSpreadsheet(SpreadsheetApp.getActiveSpreadsheet());
};

// If you need to pass in a dynamic spreadsheetId
function readRowsFromSpreadsheetId(spreadsheetId){
  readRowsFromSpreadsheet(SpreadsheetApp.openById(spreadsheetId));
};

function readRowsFromSpreadsheet(ss){
  var sheets = ss.getSheets();
  for (var k = 0; k < sheets.length; k++){
    var sheet = sheets[k];
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var values = rows.getValues();
    var headers = values[0];

    // Clear out the existing contents of the collection, if there are contents
    var d = kinveyGet(sheet.getName());
    if (d.getContentText() !== "[]"){
      kinveyClear(sheet.getName());
    }


    for (var i = 1; i < numRows; i++) {
      var row = values[i];
      var obj = {};
      for (var j = 0; j < row.length; j++){
        if (row[j] instanceof Date){
          obj[headers[j]] = "ISODate(\"" + Utilities.formatDate(row[j], "UTC", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") + "\")";
        } else {
          obj[headers[j]] = row[j];
        }
      }
      kinveyInsert(sheet.getName(), obj);
    }
  }
};



function makeAuthHeader(){
  var kinveyParams = getKinveyParameters();
  
  var appId = kinveyParams.kinvey_app_id;
  var masterSecret = kinveyParams.kinvey_master_secret;

  var tmp = appId + ":" + masterSecret;
  var authString = "Basic " + Utilities.base64Encode(tmp, Utilities.Charset.UTF_8);
  return authString;
};

function kinveyClear(className){
  var appId = getKinveyParameters().kinvey_app_id;

  var headers = {
    "Authorization": makeAuthHeader(),
  };
  
  var url = "https://baas.kinvey.com/appdata/" + appId + "/" + className + "/?query%3D%7B%7D";
  
  var options = {
    "method" : "delete",
    "headers" : headers,
    "contentType" : "application/json"
   };

  return UrlFetchApp.fetch(url, options);

};

function kinveyInsert(className, params){
  var appId = getKinveyParameters().kinvey_app_id;

var headers = {
    "Authorization": makeAuthHeader(),
  };
  
  var url = "https://baas.kinvey.com/appdata/" + appId + "/" + className;
  var payload = Utilities.jsonStringify(params);
  
  var options = {
    "method" : "post",
    "payload" : payload,
    "headers" : headers,
    "contentType" : "application/json"
   };

  return UrlFetchApp.fetch(url, options);
}

function kinveyGet(className){
  var appId = getKinveyParameters().kinvey_app_id;

  var headers = {
    "Authorization": makeAuthHeader(),
  };
  
  var url = "https://baas.kinvey.com/appdata/" + appId + "/" + className + "/?query%3D%7B%7D";
  
  var options = {
    "method" : "get",
    "headers" : headers,
    "contentType" : "application/json"
   };

  return UrlFetchApp.fetch(url, options);

};

Kinvey Uploader

Use this snippet to upload a spreadsheet to Kinvey, creating individual collections from each of the different sheets within your spreadsheet.

To use you need to update three lines

• kinvey_app_id: " YOUR APP ID ",
• kinvey_master_secret: " YOUR MASTER SECRET "
• var id = " YOUR GOOGLE SPREADSHEET ID ";

Your Kinvey App ID and Master Secret can be found on the Kinvey console. The Spreadsheet ID can be found in the URL of the Google Spreadsheet.