Push/Pull data from Google Sheets to Kentico Custom Tables

Trevor Chinn asked on February 3, 2017 05:20

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

Correct Answer

Trevor Fayas answered on February 3, 2017 15:28

Firstly, great question and good explanation of it.

Ideally i would pull from Google through their API, but as you said it requires oath (which can be done programatically, google has a document on how). That way it's automated and there's a single point of code vs. multiple systems which can be hard to track if someone else has to manage this later on.

CSV upload is indeed more for one off type of things, it is rather annoying to have to manage yourself and physically perform this action, and not maintainable in the long run. CSV to FTP automation and then using code to pull that CSV down and processes it can help make the task less 'hands on' but again we have run into cases where it 'stops working' and clients ask why, and it turns out the separate task to upload the CSV to the FTP (not controlled by us) broke, so we couldn't do anything.

Integration bus is best for when Kentico is the source i believe, but i am not knowledgeable enough in that particular area to really say. I could very well be wrong.

Pushing from GS to Kentico through a Web API requires then the control of the 'pushes' to be in Google vs. Kentico, having them in a Scheduled task allows for timed executions, as well as "run now" on the task can run it anytime you wish.

So your method is a good one, but ideally you should try sifting through Google's API documents on how to send OATH requests, put the oath creds in the Kentico Settings and set up a scheduled task to perform this.

0 votesVote for this answer Unmark Correct answer

Recent Answers


Trevor Chinn answered on February 6, 2017 05:01

Hi Trevor,

Thanks for your feedback, and the link to the Google API documents. I have done work with the sheets API in the past (using scheduled tasks but not in a Kentico environment), however one of the main issues with the automated pull through a task would be the (admittedly unlikely) case where someone is half-way through editing the data when the task runs, and it pulls incomplete data over to the live site. But yes, that was the other option I explored before deciding on the button, as it seems like the cleanest method of accomplishing the goal (having all the code relevant to the data transfer in one spot).

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on February 6, 2017 22:19

Keep in mind you can still have it in the Scheduled Tasks, but have the task disabled, then you can click to manually run it whenever, and still have it ready to do automated if you wish. Otherwise a custom UI page with a button would suffice!

0 votesVote for this answer Mark as a Correct answer

Trevor Chinn answered on February 8, 2017 09:54

I was hoping to get a couple of different peoples' opinions on the matter, but I suppose we have already covered the vast majority of what can be said. Thanks for you input, Trevor. I'll mark your answer as correct so that it allows the thread to die peacefully :)

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.