Syncing Address Information from SmartHR API to Google Sheets with Google Apps Script

Tadashi Shigeoka ·  Thu, March 2, 2023

I’ll introduce a method to sync address information from SmartHR API to Google Sheets using Google Apps Script.

Background: Want to Share Only Address Information from SmartHR

There was a need to share only employee address information with people who cannot be granted SmartHR administrator privileges (such as general affairs staff). This was resolved by syncing address information from SmartHR API to Google Sheets using Google Apps Script.

Sample Code: Syncing Address Information from SmartHR API to Google Sheets with Google Apps Script

Referencing SmartHR APIとGoogle Apps Scriptを使って社内向け社員一覧の取得を自動化する | DevelopersIO, I created the following script:

function listEmployee() {
    //SmartHR API key (confidential information, so set in properties beforehand)
    const TOKEN = PropertiesService.getScriptProperties().getProperty("SMARTHR_APIKEY");
    //Organization name (subdomain name registered in SmartHR)
    const ORG = PropertiesService.getScriptProperties().getProperty("ORG");
    //How many pages to retrieve (depends on number of registered employees)
    const LAST_PAGE = 1;
    //Maximum 100 items per page
    const PER_PAGE = 100;

    // Get currently active spreadsheet
    const spreadsheet = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
    // Specify sheet name
    const sheet = spreadsheet.getActiveSheet();;
    //Get last row of existing data
    const lastRowNum = sheet.getLastRow();
    //Add 1 row so it can be deleted even if empty
//    sheet.appendRow([""]);
    //Delete all existing data rows listed in spreadsheet
//    sheet.deleteRows(1, lastRowNum);

    //Insert header in row 1
    sheet.appendRow([
        "Employee Number",
        "Last Name",
        "First Name",
        "Last Name (Kana)",
        "First Name (Kana)",
        "Last Name (Business Name)",
        "First Name (Business Name)",
        "Last Name (Business Name Kana)",
        "First Name (Business Name Kana)",
        "Postal Code",
        "Prefecture",
        "City",
        "Street Address",
        "Building/Room Number",
        "Phone Number"
    ]);

    //Filter and process all loaded employee information and add to list.
    for (var page = 1; page <= LAST_PAGE; page++) {
        const endpointUrl = "https://" + ORG + ".smarthr.jp/api/v1/crews?page=" + page + "&per_page=" + PER_PAGE + "&access_token=" + TOKEN;
        const response = UrlFetchApp.fetch(endpointUrl);
        const json = JSON.parse(response.getContentText());

        for (var i = 0; i < PER_PAGE; i++) {
            //Logger.log(json[i]);
            if (json[i] != null) {
                const last_name = json[i]["last_name"];;
                const first_name = json[i]["first_name"];
                const last_name_yomi = json[i]["last_name_yomi"];
                const first_name_yomi = json[i]["first_name_yomi"];
                const business_last_name = json[i]["business_last_name"];
                const business_first_name = json[i]["business_first_name"];
                const business_last_name_yomi = json[i]["business_last_name_yomi"];
                const business_first_name_yomi = json[i]["business_first_name_yomi"];
                const emp_code = json[i]["emp_code"];
                const address = json[i]["address"];
                const tel_number = json[i]["tel_number"];


                //Employee information meeting various conditions is added to the list
                sheet.appendRow([
                    emp_code,
                    last_name,
                    first_name,
                    last_name_yomi,
                    first_name_yomi,
                    business_last_name,
                    business_first_name,
                    business_last_name_yomi,
                    business_first_name_yomi,
                    address && address.zip_code,
                    address && address.pref,
                    address && address.city,
                    address && address.street,
                    address && address.building,
                    tel_number
                ]);
            }
        }
    }
}

That’s all from the Gemba - I successfully synced address information from SmartHR API to Google Sheets using Google Apps Script.