Push/Pull data from Google Sheets to Kentico Custom Tables

Hi there,

A bit of a "best practices" sort of question, since things have calmed down a bit and I've managed to deploy my "solution", thus I can now reflect on how to do it better next time.

We had the need to take data that was being managed in a google sheet (GS) (due to the powerful collaborative capabilities, data filtering and formulas, all baked in without us having to build out a web interface for DB based data) and periodically push that over to a Kentico custom table such that it could be spat out by a repeater.

It was originally being done by exporting a sheet into a CSV, then manually imported using the Kentico Import Toolkit, however that was horrendously inefficient (man-hours wise).

So I looked at a few options, primarily in the three:

• Push from GS to Kentico's integration bus
• Somehow pull the data from Google sheets (custom data source)
• Push from GS to Kentico through a custom Web API endpoint

I decided on the third as the integration bus didn't seem to offer any benefits (that I could see) in this case, and pulling from Google sheets required Oauth2 based authentication (because it is a private sheet) and thus wasn't seemingly feasible.

It ended up like this:

public IHttpActionResult Post([FromBody]Payload bodyContents)
    {
        UserInfo user = null;
        user = AuthenticationHelper.AuthenticateUser(bodyContents.Credentials.username, bodyContents.Credentials.password, SiteContext.CurrentSiteName);

        if (user == null)
        {

            return Ok("Username/password don't match");
        }

        //Get the custom table object that holds all the data
        string customTableClassName = "customtable.***";
        DataClassInfo customTable = DataClassInfoProvider.GetDataClassInfo(customTableClassName);

        //Clear out the current table contents because we will overwrite everything each time
        //It ends up being more efficient than trying to update each record individually, and is less prone to errors
        QueryParameters qp = new QueryParameters("TRUNCATE TABLE customtable_***", null, QueryTypeEnum.SQLQuery);
        ConnectionHelper.ExecuteQuery(qp);

        if (customTable != null && bodyContents.DataArray != null)
        {
            //Need the headers row to properly bind data to the correct column name
            List<string> headersRow = bodyContents.DataArray[0];
            int rowLength = headersRow.Count;

            for (int i = 1; i < bodyContents.DataArray.Count; i++)
            {
                List<string> currentRow = bodyContents.DataArray[i];

                // Creates a new custom table item
                CustomTableItem newCustomTableItem = CustomTableItem.New(customTableClassName);
                for(int j = 0; j < rowLength; j++)
                {
                    newCustomTableItem.SetValue(headersRow[j], currentRow[j]);
                }

                // Save the new custom table record into the database
                newCustomTableItem.Insert();
            }
        }

        return Ok("Successfully updated: " + bodyContents.DataArray.Count + "rows.");
    }
}

And on the google side, there is a bit of Google Script action:

var data = {};

function updateKentico() {
  //Set up data range
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Export Data');
  var startRow = 1;  // First row of data to process
  var numRows = 1000;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);

  // Fetch values for each row in the Range.
  data.DataArray = dataRange.getValues();

  //Filter out blank rows
  for(i = 0; i<data.DataArray.length; i++)
  {
    if(data.DataArray[i][0] == "")
    {
      data.DataArray.splice(i, data.DataArray.length-1);
      break;
    }
  }

  //Set up API call
  var url = "***";
  var payload = JSON.stringify(data);

  var headers = { "Accept":"application/json", 
             "Content-Type":"application/json", 
             "Authorization":"Basic _authcode_"
            };

  var options = { "method":"POST",
             "headers": headers,
             "payload" : payload
            };
  var response = UrlFetchApp.fetch(url, options);

  //Alert user of success/failure
  DisplayResponse(response);
}

function DisplayResponse(responseJson)
{
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert(responseJson);
}

//Opens the client side HTML dialog box to take the username/password
function GetCredentials()
{
   var html = HtmlService.createHtmlOutputFromFile('prompt')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi()
  .showModalDialog(html, 'Please enter your Kentico login info');
}

//Called from the client side to update the credentials to send to Kentico
function SetCredentials(username, password)
{
  data.Credentials = {"username": username, "password": password};
  updateKentico();
}

//Create the UI elements for the user
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Kentico')
      .addItem('Publish to live site', 'publishButton')
      .addToUi();
}

//Click handler for the publish to kentico button
function publishButton() {
  GetCredentials();
}

What I'm quite interested in, is hearing from people who have a bit more experience with Kentico... in your opinion, what would be the ideal "Kentico" way to deal with this situation?

*NB: plaintext passwords over post is not secure, but when asked the person requesting the feature stated that they wanted it sooner than secure. SHA256 is the hopeful next evolution of the password-sending step :)