Syncing Address Information from SmartHR API to Google Sheets with Google Apps Script
I’ll introduce a method to sync address information from SmartHR API to Google Sheets using Google Apps Script.
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.
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.