Schedule Cron Jobs with Azure Functions

Contents

Overview

This article introduces how to set up cron jobs in Azure Functions to periodically post data into Kintone.

Sample Image

Two Kintone Apps are used in this example: an Expense Report App and an Expense Summary App. Users use the Expense Report App to record their expenses, such as travel expenses. The Expense Report App has a workflow set up, where users will need to get approval from their manager when the expense is submitted.

A scheduled program runs at 2:00AM on the first day of the month. It sums up all of the expenses for each department from the previous month, and adds a record to the Expense Summary App for each department.

Prepare Azure Functions

Follow the Create your first function in the Azure portal (External link) article on the Microsoft Azure website to set up Azure Functions.

Navigate to the Advanced Tools menu, then to Go->. Select CMD from the Debug Menu in the header to display the Kudu (External link) console. Install the following packages through the console:

  • request (package to execute HttpRequests)

    1

    $ npm install request --save-dev

  • request-promise (package to use Promises)

    1

    $ npm install request-promise --save-dev

  • moment (package to operate on dates)

    1

    $ npm install moment --save-dev

Refer to Get Multiple Records with Azure Functions on how to install the packages.

Prepare the Kintone App

This article uses the following App template, containing the Expense Report App and the Expense Summary App:
AzureFunctions integration (scheduled event).zip (External link)

Refer to the App Templates (External link) page in the Kintone Site for how to import App templates. Refer to the Creating an App from a Template (External link) page on how to create Apps from templates.

Sample Code

The code in this integration will process as follows:

  1. Get data of all approved records in the Expense Report App** from the previous month.
  2. Sum up travel expenses by department.
  3. Add a record to the Expense Summary App for each department, containing data of the summed up expenses.

Prepare the following code for Azure Functions. Replace BASE_URL, APP_ID_AGG, APP_ID_AGG_TRANS, API_TOKEN_AGG and API_TOKEN_TRANS with their relative information. Refer to the Generating API Tokens (External link) article on the Kintone Help site for more information on API Tokens.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101

var rq = require('request-promise');
var moment = require('moment');

var BASE_URL = 'https://{subdomain}.kintone.com/k/v1/'; // Enter your subdomain
var APP_ID_AGG = 'xx'; // Expense Summary AppID
var APP_ID_AGG_TRANS = 'xx'; // Expense Report AppID
var API_TOKEN_AGG = 'xxxxxxxxx'; // API token of Expense Summary (Permissions: View/Add)
var API_TOKEN_TRANS = 'xxxxxxxxx'; // API token of Expense Report (Permissions: View)
var TARGET_DEPARTMENT = ['Sales', 'General Affairs', 'Accounting', 'Developer'];

module.exports = function(context, myTimer) {
  'use strict';

  function aggregateTransportationCost(target_dept) {
    var query = 'Status = "Completed" and ' +
                'year = "' + moment().year() + '" and ' +
                'month = "' + moment().subtract(1, 'months').format('M') + '" and ' +
                'department in ("' + target_dept + '")';
    var body = {
      'app': APP_ID_AGG_TRANS,
      'query': query,
      'fields': ['sum_money']
    };
    var get_option = {
      url: BASE_URL + 'records.json',
      method: 'GET',
      json: body,
      headers: {
        'X-Cybozu-API-Token': API_TOKEN_TRANS
      }
    };
    return rq(get_option);
  }

  // Summary Process
  function aggregate(departments, opt_cnt, opt_resultObj) {
    var i = opt_cnt || 0;
    var obj = opt_resultObj || {};
    var dept = departments[i];

    // when process for all department is finished   return
    if (!dept) {
      return obj;
    }

    //
    return aggregateTransportationCost(dept).then(function(resp) {
      var sum = 0;
      for (var j in resp.records) {
        if (!Object.prototype.hasOwnProperty.call(resp.records, j)) {
          continue;
        }
        // sum += parseInt(resp.records[j].sum_money.value, 10);
        sum += +resp.records[j].sum_money.value;
      }
      obj[dept] = sum;
      return aggregate(departments, parseInt(i, 10) + 1, obj);
    });
  }

  //
  function postAggregateData(obj) {
    var records = [];
    for (var i in obj) {
      if (!Object.prototype.hasOwnProperty.call(obj, i)) {
        continue;
      }
      records.push({
        'year': {'value': moment().year()},
        'month': {'value': moment().subtract(1, 'months').format('M')},
        'depertment': {'value': i},
        'total_fare': {'value': obj[i]}
      });
    }

    var post_option = {
      url: BASE_URL + 'records.json',
      method: 'POST',
      headers: {
        'X-Cybozu-API-Token': API_TOKEN_AGG,
        'Content-Type': 'application/json'
      },
      json: {
        'app': APP_ID_AGG,
        'records': records
      }
    };
    //
    return rq(post_option);
  }

  aggregate(TARGET_DEPARTMENT)
    .then(postAggregateData)
    .then(function(resp) {
      context.done();
    })
    .catch(function(e) {
      context.log('error');
      context.fail();
    });
};

Set up the Scheduler on Azure Functions

Set up the scheduler using Cron expressions in the Schedule field.

1

0 0 2 1 * *

Use the below table as reference for scheduled timings.

Schedule Cron expression
{second} {minute} {hour} {day} {month} {day of the week}
Trigger every 5 minutes 0 */5 * * * *
Trigger every hour 0 0 * * * *
Trigger every two hours 0 0 */2 * * *
Trigger every hour from 9 AM to 5 PM 0 0 9-17 * * *
Trigger every day at 9:30 AM 0 30 9 * * *
Trigger every weekday at 9:30 AM 0 30 9 * * 1-5

Test the Integration

The cron is set up to run every day at 2:00AM. Check the Kintone App after this time to see if new records were automatically posted.

When the code runs, Azure Functions should first retrieve records from the Expense Report App. The request is queried so that records that have the status of Completed, and are from the previous month are retrieved.

Azure Functions then should aggregate the total expenses per Department. A record should then be added in to the Expense Summary App for every Department.

Reference