For Zoho services only


I'm currently part of a wider delivery team at Ascent Business Solutions, recognised as a leading Zoho Premium Solutions Partner in the United Kingdom.

Ascent Business Solutions support organisations with everything from targeted technical fixes through to full Zoho CRM implementations and long-term platform adoption. Working as a team rather than a one-person consultancy allows projects to move forward consistently, with access to the right skills at each stage.

The team I manage specialises in API integrations between Zoho and third-party finance and commerce platforms such as Xero, Shopify, WooCommerce, and eBay. Much of our work involves solving integration challenges that fall outside standard documentation, supporting new ideas, new sectors, and evolving business models.

Success is measured through practical outcomes and return on investment, ranging from scaling small operations into high-turnover businesses to delivering rapid gains through online payments, automation, and streamlined digital workflows.

If you are looking for structured Zoho expertise backed by an established consultancy, you can contact Ascent Business Solutions on 0121 392 8140 (UK), email info@ascentbusiness.co.uk, or visit https://www.ascentbusiness.co.uk.
Zoho People and Zoho Analytics: Push all Employee Salary Data to Analytics

Zoho People and Zoho Analytics: Push all Employee Salary Data to Analytics

Category: Zoho People
Hits: 15
What?
Pretty self-explanatory really. Something to be cautious about but it took me so long to do that I needed an article here to help me if ever I get asked to do it again. Siimply put, Zoho Analytics can't see the Zoho People Salary table within the built-in integration / synchronization.

Why?
My use-case is for a client who has over 1200 employees but similar to performance goals, we need to write code to push the salary data out to a Zoho Analytics table.

How?
Sounds like you could just do as we did with the P_Goals table but I need this to run every night picking up salary changes as well. I don't want to use up 1200+ API calls either to do it which my script for performance goals did. The below will do it with 7 calls (to get 7 pages of 200 records) and 1 call to push into Zoho Analytics...

Create the Zoho Analytics table
You can do this any way you find best but this is how I do it as quick as I can:
  1. Create a blank Excel file / spreadsheet / csv
  2. Give it the headings: ID,Employee ID,Effective From,Currency,Amount Per Annum,Created Time,Modified Time,Created By,Modified By,Pay Schedule
  3. Enter sample data in the first 2 columns of the 2nd row to convince Analytics there is some data in the table we are going to import.
  4. I'm going to save this file as Zoho_Analytics_Salaries.xlsx
  5. Login to Zoho Analytics and browse to your Zoho People workspace
  6. Click the big plus icon in the left sidebar at the top, then click on New Table / Import Data
  7. Select "Files" (has the icon of MS Excel on it)
  8. Give it a table name. I've used my creative mind and gone with Salary
  9. File Type I said was Excel and then clicked on "Choose File" (with data location set to "Local Drive")
  10. Click on Next, have a quick glance of the preview and then click on Next
  11. First row contains column names = Yes, leave everything else as plain text except for Description which you should change to a Multi Line Text.
  12. Click on Create.

A few more things to configure
  1. Get the ZohoAnalytics Org ID:
    1. Login to ZohoAnalytics and ensure you are at the "Home" level
    2. Click on the cog icon in the top-right for "Settings"
    3. You should be in the "Organization Settings"
    4. Your Org ID is in the URL as the last number after the slash /org-details/###### (eg. "123456789")
    5. Note this down for later use
  2. Create a connection to Zoho Analytics:
    1. Above the function in Zoho People > Connections > Create Connection
    2. Select Zoho Analytics and give the required scopes. We need ZohoAnalytics.data.create (I've cheated and gone for ZohoAnalytics.fullaccess.all)
    3. Note the connection link name (mine is called "zanalytics")

Schedule the function (for Zoho People v5)
  1. Login to Zoho People as an Administrator
  2. Click on the cog icon in the top right to go to Settings
  3. Click on "Employee Information"
  4. Click on "Automation"
  5. Expand "Schedulers"
  6. "Add Custom Scheduler"
    1. I'm calling mine "Scheduled Salary Sync"
    2. It will run at 01:00 AM starting from tomorrow.
    3. I set the Frequency to "Daily"
    4. And gave it the code below
  7. Save

the Code
copyraw 
/* ******************************************************************************* 
 Function:       void Check_for_Salary()
 Label:          Fn - Push Salary Data to Analytics 
 Trigger:        Workflow when an objective is created or edited. 
 Purpose:        Pushes the data and columns we need to Zoho Analytics to produce an Objectives report with the requested columns. 
 Inputs:         int p_RecordID 
 Outputs:        - 
  
 Date Created:   2026-03-26 (Joel Lipman) 
                 - Initial release 
 Date Modified:    ??? 
                 - ??? 
				 
				 https://www.zoho.com/people/api/compensation/fetch_salary.html
				 https://www.zoho.com/analytics/api/v2/bulk-api/import-data/existing-table.html
 ******************************************************************************* */
// 
// init 
v_StartIndex = 1;
v_CountTotal = 0;
l_ColumnsData = List();
l_Pages = {1,2,3,4,5,6,7};
//
v_Headings = "ID,Employee ID,Effective From,Currency,Amount Per Annum,Created Time,Modified Time,Created By,Modified By,Pay Schedule";
l_CsvRows.add(v_Headings.toList().toString());
// 
// the zoho analytics org ID noted earlier 
v_AnalyticsOrgID = "123456879";
// 
// in analytics, browse to the target table and note the URL IDs after workspace and view 
v_WorkspaceID = "1234567000008912345";
v_ViewID = "9876543000002198765";
//
// loop through all employees, this will do 1400
for each  v_Page in l_Pages
{
	// 
	// query goals endpoint 
	v_Endpoint_Salary = "https://people.zoho.com/api/compensation/v1/salary?mode=all&startIndex=" + v_StartIndex;
	r_SalaryData = invokeurl
	[
		url :v_Endpoint_Salary
		type :GET
		connection:"zpeople"
	];
	//info r_SalaryData;
	// 
	// parse the response loop through each record returned 
	m_SalaryResponse = ifnull(r_SalaryData.get("response"),Map());
	l_SalaryRecords = ifnull(m_SalaryResponse.get("result"),List());
	for each  m_SalaryRecord in l_SalaryRecords
	{
		// 
		// start an increment for counting the records processed 
		v_CountTotal = v_CountTotal + 1;
		v_StartIndex = v_StartIndex + 1;
		m_ColumnsData = Map();
		//
		// date transform
		v_DateTransform = ifnull(m_SalaryRecord.get("Effective_From"),zoho.currentdate.toString("dd-MMM-yyyy"));
		m_DateMonthMap = {"Jan":"01","Feb":"02","Mar":"03","Apr":"04","May":"05","Jun":"06","Jul":"07","Aug":"08","Sep":"09","Oct":"10","Nov":"11","Dec":"12"};
		l_DateEffectiveFromParts = v_DateTransform.toList("-");
		v_SqlDate = if(l_DateEffectiveFromParts.size() > 1,l_DateEffectiveFromParts.get(2) + "-" + m_DateMonthMap.get(l_DateEffectiveFromParts.get(1)) + "-" + l_DateEffectiveFromParts.get(0),null);
		//
		// v2: data
		m_ColumnsData.put("ID",m_SalaryRecord.get("Salary_Id"));
		m_ColumnsData.put("Employee ID",m_SalaryRecord.get("Erecno"));
		m_ColumnsData.put("Effective From",v_SqlDate);
		m_ColumnsData.put("Currency",m_SalaryRecord.get("Currency"));
		m_ColumnsData.put("Amount Per Annum",m_SalaryRecord.get("CTC"));
		m_ColumnsData.put("Created Time",zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss"));
		m_ColumnsData.put("Modified Time",zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss"));
		m_ColumnsData.put("Pay Schedule",m_SalaryRecord.get("Pay_Schedule"));
		l_ColumnsData.add(m_ColumnsData);
	}
}
//
// authorisation header
m_Header = Map();
m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID);
//
// v2 config
m_Config = Map();
m_Config.put("importType","updateadd");
m_Config.put("fileType","json");
m_Config.put("autoIdentify","true");
// update on key
m_Config.put("matchingColumns",{"ID"});
//
// v2 data
m_Params = Map();
m_Params.put("CONFIG",m_Config.toString());
m_Params.put("DATA",l_ColumnsData);
//
v_Endpoint_v2 = "https://analyticsapi.zoho.com/restapi/v2/workspaces/" + v_WorkspaceID + "/views/" + v_ViewID + "/data";
info v_Endpoint_v2;
//
r_FileUpload = invokeurl
[
	url :v_Endpoint_v2
	type :POST
	parameters:m_Params
	headers:m_Header
	connection:"abanalytics"
];
info r_FileUpload;
//
info "Processed " + v_CountTotal + " record(s)";
  1.  /* ******************************************************************************* 
  2.   Function:       void Check_for_Salary() 
  3.   Label:          Fn - Push Salary Data to Analytics 
  4.   Trigger:        Workflow when an objective is created or edited. 
  5.   Purpose:        Pushes the data and columns we need to Zoho Analytics to produce an Objectives report with the requested columns. 
  6.   Inputs:         int p_RecordID 
  7.   Outputs:        - 
  8.   
  9.   Date Created:   2026-03-26 (Joel Lipman) 
  10.                   - Initial release 
  11.   Date Modified:    ??? 
  12.                   - ??? 
  13.   
  14.                   https://www.zoho.com/people/api/compensation/fetch_salary.html 
  15.                   https://www.zoho.com/analytics/api/v2/bulk-api/import-data/existing-table.html 
  16.   ******************************************************************************* */ 
  17.  // 
  18.  // init 
  19.  v_StartIndex = 1; 
  20.  v_CountTotal = 0; 
  21.  l_ColumnsData = List(); 
  22.  l_Pages = {1,2,3,4,5,6,7}; 
  23.  // 
  24.  v_Headings = "ID,Employee ID,Effective From,Currency,Amount Per Annum,Created Time,Modified Time,Created By,Modified By,Pay Schedule"; 
  25.  l_CsvRows.add(v_Headings.toList().toString()); 
  26.  // 
  27.  // the zoho analytics org ID noted earlier 
  28.  v_AnalyticsOrgID = "123456879"; 
  29.  // 
  30.  // in analytics, browse to the target table and note the URL IDs after workspace and view 
  31.  v_WorkspaceID = "1234567000008912345"; 
  32.  v_ViewID = "9876543000002198765"; 
  33.  // 
  34.  // loop through all employees, this will do 1400 
  35.  for each  v_Page in l_Pages 
  36.  { 
  37.      // 
  38.      // query goals endpoint 
  39.      v_Endpoint_Salary = "https://people.zoho.com/api/compensation/v1/salary?mode=all&startIndex=" + v_StartIndex; 
  40.      r_SalaryData = invokeurl 
  41.      [ 
  42.          url :v_Endpoint_Salary 
  43.          type :GET 
  44.          connection:"zpeople" 
  45.      ]; 
  46.      //info r_SalaryData; 
  47.      // 
  48.      // parse the response loop through each record returned 
  49.      m_SalaryResponse = ifnull(r_SalaryData.get("response"),Map()); 
  50.      l_SalaryRecords = ifnull(m_SalaryResponse.get("result"),List()); 
  51.      for each  m_SalaryRecord in l_SalaryRecords 
  52.      { 
  53.          // 
  54.          // start an increment for counting the records processed 
  55.          v_CountTotal = v_CountTotal + 1; 
  56.          v_StartIndex = v_StartIndex + 1; 
  57.          m_ColumnsData = Map(); 
  58.          // 
  59.          // date transform 
  60.          v_DateTransform = ifnull(m_SalaryRecord.get("Effective_From"),zoho.currentdate.toString("dd-MMM-yyyy")); 
  61.          m_DateMonthMap = {"Jan":"01","Feb":"02","Mar":"03","Apr":"04","May":"05","Jun":"06","Jul":"07","Aug":"08","Sep":"09","Oct":"10","Nov":"11","Dec":"12"}; 
  62.          l_DateEffectiveFromParts = v_DateTransform.toList("-"); 
  63.          v_SqlDate = if(l_DateEffectiveFromParts.size() > 1,l_DateEffectiveFromParts.get(2) + "-" + m_DateMonthMap.get(l_DateEffectiveFromParts.get(1)) + "-" + l_DateEffectiveFromParts.get(0),null); 
  64.          // 
  65.          // v2: data 
  66.          m_ColumnsData.put("ID",m_SalaryRecord.get("Salary_Id")); 
  67.          m_ColumnsData.put("Employee ID",m_SalaryRecord.get("Erecno")); 
  68.          m_ColumnsData.put("Effective From",v_SqlDate); 
  69.          m_ColumnsData.put("Currency",m_SalaryRecord.get("Currency")); 
  70.          m_ColumnsData.put("Amount Per Annum",m_SalaryRecord.get("CTC")); 
  71.          m_ColumnsData.put("Created Time",zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss")); 
  72.          m_ColumnsData.put("Modified Time",zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss")); 
  73.          m_ColumnsData.put("Pay Schedule",m_SalaryRecord.get("Pay_Schedule")); 
  74.          l_ColumnsData.add(m_ColumnsData); 
  75.      } 
  76.  } 
  77.  // 
  78.  // authorisation header 
  79.  m_Header = Map(); 
  80.  m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID); 
  81.  // 
  82.  // v2 config 
  83.  m_Config = Map(); 
  84.  m_Config.put("importType","updateadd"); 
  85.  m_Config.put("fileType","json"); 
  86.  m_Config.put("autoIdentify","true"); 
  87.  // update on key 
  88.  m_Config.put("matchingColumns",{"ID"}); 
  89.  // 
  90.  // v2 data 
  91.  m_Params = Map(); 
  92.  m_Params.put("CONFIG",m_Config.toString()); 
  93.  m_Params.put("DATA",l_ColumnsData); 
  94.  // 
  95.  v_Endpoint_v2 = "https://analyticsapi.zoho.com/restapi/v2/workspaces/" + v_WorkspaceID + "/views/" + v_ViewID + "/data"; 
  96.  info v_Endpoint_v2; 
  97.  // 
  98.  r_FileUpload = invokeurl 
  99.  [ 
  100.      url :v_Endpoint_v2 
  101.      type :POST 
  102.      parameters:m_Params 
  103.      headers:m_Header 
  104.      connection:"abanalytics" 
  105.  ]; 
  106.  info r_FileUpload; 
  107.  // 
  108.  info "Processed " + v_CountTotal + " record(s)"; 

Error(s) Encountered
  • copyraw 
    { 
    "status": "failure", 
    "summary": "COMMON_INTERNAL_SERVER_ERROR", 
    "data": { 
        "errorCode": 7005, 
        "errorMessage": "Sorry, an unexpected error occurred when performing this operation. The error has been logged and will be looked into.\n\nIt would be of great help if you could provide us with additional information using the ''Feedback'' link." 
    } 
}
    1.  { 
    2.      "status": "failure", 
    3.      "summary": "COMMON_INTERNAL_SERVER_ERROR", 
    4.      "data": { 
    5.          "errorCode": 7005, 
    6.          "errorMessage": "Sorry, an unexpected error occurred when performing this operation. The error has been logged and will be looked into.\n\nIt would be of great help if you could provide us with additional information using the ''Feedback'' link." 
    7.      } 
    8.  } 
    I tried uploading a CSV to Zoho Analytics... Specifically the "file" attribute in the invokeUrl... I spent too long but thought I could send DATA instead of FILE. At time of print, the official documentation didn't document how to do this. So I found this way to do it as long as it remained in JSON as well.

Caveat(s)
  • Created Time will always be the time when updating as well... haven't found a way around this as I would need to check if the record exists in Zoho Analytics before updating it... This would use up more API calls then I'd care to.


Scrap Notes - Not used for the above but things I tried and didn't go anywhere
copyraw 
/* ******************************************************************************* 
 Function:       void Check_for_Salary()
 Label:          Fn - Push Salary Data to Analytics 
 Trigger:        Workflow when an objective is created or edited. 
 Purpose:        Pushes the data and columns we need to Zoho Analytics to produce an Objectives report with the requested columns. 
 Inputs:         int p_RecordID 
 Outputs:        - 
  
 Date Created:   2026-03-26 (Joel Lipman) 
                 - Initial release 
 Date Modified:    ??? 
                 - ??? 
				 
				 https://www.zoho.com/people/api/compensation/fetch_salary.html
				 https://www.zoho.com/analytics/api/v2/bulk-api/import-data/existing-table.html
 ******************************************************************************* */
// 
// init 
v_StartIndex = 1;
v_CountTotal = 0;
l_CsvRows = List();
l_ColumnsData = List();
l_Pages = {1,2,3,4,5,6,7};
//
v_Headings = "ID,Employee ID,Effective From,Currency,Amount Per Annum,Created Time,Modified Time,Created By,Modified By,Pay Schedule";
l_CsvRows.add(v_Headings.toList().toString());
// 
// the zoho analytics org ID noted earlier 
v_AnalyticsOrgID = "123456879";
// 
// in analytics, browse to the target table and note the URL IDs after workspace and view 
v_WorkspaceID = "1234567000008912345";
v_ViewID = "9876543000002198765";
//
// loop through all employees, this will do 1400
for each  v_Page in l_Pages
{
	// 
	// query goals endpoint 
	v_Endpoint_Salary = "https://people.zoho.com/api/compensation/v1/salary?mode=all&startIndex=" + v_StartIndex;
	r_SalaryData = invokeurl
	[
		url :v_Endpoint_Salary
		type :GET
		connection:"zpeople"
	];
	//info r_SalaryData;
	// 
	// parse the response loop through each record returned 
	m_SalaryResponse = ifnull(r_SalaryData.get("response"),Map());
	l_SalaryRecords = ifnull(m_SalaryResponse.get("result"),List());
	for each  m_SalaryRecord in l_SalaryRecords
	{
		// 
		// start an increment for counting the records processed 
		v_CountTotal = v_CountTotal + 1;
		v_StartIndex = v_StartIndex + 1;
		l_CsvRow = List();
		m_ColumnsData = Map();
		//
		// v2: file
		l_CsvRow.add("\"" + m_SalaryRecord.get("Salary_Id") + "\"");
		l_CsvRow.add("\"" + m_SalaryRecord.get("Erecno") + "\"");
		//
		// date transform
		v_DateTransform = ifnull(m_SalaryRecord.get("Effective_From"),zoho.currentdate.toString("dd-MMM-yyyy"));
		m_DateMonthMap = {"Jan":"01","Feb":"02","Mar":"03","Apr":"04","May":"05","Jun":"06","Jul":"07","Aug":"08","Sep":"09","Oct":"10","Nov":"11","Dec":"12"};
		l_DateEffectiveFromParts = v_DateTransform.toList("-");
		v_SqlDate = if(l_DateEffectiveFromParts.size() > 1,l_DateEffectiveFromParts.get(2) + "-" + m_DateMonthMap.get(l_DateEffectiveFromParts.get(1)) + "-" + l_DateEffectiveFromParts.get(0),null);
		l_CsvRow.add(v_SqlDate);
		//
		// other fields
		l_CsvRow.add(m_SalaryRecord.get("Currency"));
		l_CsvRow.add(m_SalaryRecord.get("CTC"));
		l_CsvRow.add(zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss"));
		l_CsvRow.add(zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss"));
		l_CsvRow.add(null);
		l_CsvRow.add(null);
		l_CsvRow.add(m_SalaryRecord.get("Pay_Schedule"));
		l_CsvRows.add(l_CsvRow.toString());
		//
		// v2: data
		m_ColumnsData.put("ID",m_SalaryRecord.get("Salary_Id"));
		m_ColumnsData.put("Employee ID",m_SalaryRecord.get("Salary_Id"));
		m_ColumnsData.put("Effective From",v_SqlDate);
		m_ColumnsData.put("Currency",m_SalaryRecord.get("Currency"));
		m_ColumnsData.put("Amount Per Annum",m_SalaryRecord.get("CTC"));
		m_ColumnsData.put("Created Time",zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss"));
		m_ColumnsData.put("Modified Time",zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss"));
		m_ColumnsData.put("Pay Schedule",m_SalaryRecord.get("Pay_Schedule"));
		l_ColumnsData.add(m_ColumnsData);
	}
}
//
//
// generate a CSV
v_NewLine = hexToText("0A");
f_SalariesCSV = l_CsvRows.toString(v_NewLine).toFile("Salaries.csv");
f_SalariesCSV.setParamName("file");
/*
m_Config.put("dateFormat","yyyy-MM-dd");
m_Config.put("onError","setcolumnempty");
m_Config.put("skipTop",1);
*/
//v_Params = "CONFIG=" + zoho.encryption.urlEncode(m_Config.toString());
//
//
/*
// COMMON PARAMS
m_Params = Map();
m_Params.put("ZOHO_ACTION","IMPORT");
m_Params.put("ZOHO_OUTPUT_FORMAT","JSON");
m_Params.put("ZOHO_ERROR_FORMAT","JSON");
m_Params.put("ZOHO_API_VERSION","1.0");
// ACTION SPECIFIC PARAMS
m_Params.put("ZOHO_IMPORT_TYPE","UPDATEADD");
m_Params.put("ZOHO_AUTO_IDENTIFY","TRUE");
m_Params.put("ZOHO_ON_IMPORT_ERROR","SETCOLUMNEMPTY");
m_Params.put("ZOHO_CREATE_TABLE","FALSE");
//
//
*/
/*
r_SalariesCSV = invokeurl
[
	url: "https://test.com/tmp/salaries.csv"
	type: GET
];
f_SalariesCSV = r_SalariesCSV.toFile("Salaries.csv");
f_SalariesCSV.setParamName("file");
//
v_QueryParam = "";
l_Keys = m_Params.keys();
for each  v_Key in l_Keys
{
	v_QueryParam = v_QueryParam + "&" + v_Key + "=" + m_Params.get(v_Key);
}
//
*/
//
//
/*
v_OwnerEmail = zoho.encryption.urlEncode("the_super_admin_email");
v_WorkspaceName = zoho.encryption.urlEncode("Zoho People Analytics");
v_ViewName = zoho.encryption.urlEncode("Test");
v_Endpoint_v1 = "https://analyticsapi.zoho.com/api/" + v_OwnerEmail + "/" + v_WorkspaceName + "/" + v_ViewName + "?" + v_QueryParam.getSuffix("&");
//info v_Endpoint_v1;
*/
//
// authorisation header
m_Header = Map();
//m_Header.put("Content-Type","multipart/form-data");
m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID);
//info m_Header;
//
// v2 config
m_Config = Map();
m_Config.put("importType","updateadd");
m_Config.put("fileType","json");
m_Config.put("autoIdentify","true");
// update on key
m_Config.put("matchingColumns",{"ID"});
//
// v2 data
m_Params = Map();
m_Params.put("CONFIG",m_Config.toString());
m_Params.put("DATA",l_ColumnsData);
//
v_Endpoint_v2 = "https://analyticsapi.zoho.com/restapi/v2/workspaces/" + v_WorkspaceID + "/views/" + v_ViewID + "/data";
info v_Endpoint_v2;
//
r_FileUpload = invokeurl
[
	url :v_Endpoint_v2
	type :POST
	parameters:m_Params
	headers:m_Header
	connection:"zanalytics"
];
info r_FileUpload;
//
sendmail
[
	from :zoho.adminuserid
	to :"moi"
	subject :"DEBUGGING - Salary File - TO BE PERMANENTLY DELETED"
	message :m_Config
	Attachments :file:f_SalariesCSV
]
//
info "Processed " + v_CountTotal + " record(s)";
  1.  /* ******************************************************************************* 
  2.   Function:       void Check_for_Salary() 
  3.   Label:          Fn - Push Salary Data to Analytics 
  4.   Trigger:        Workflow when an objective is created or edited. 
  5.   Purpose:        Pushes the data and columns we need to Zoho Analytics to produce an Objectives report with the requested columns. 
  6.   Inputs:         int p_RecordID 
  7.   Outputs:        - 
  8.   
  9.   Date Created:   2026-03-26 (Joel Lipman) 
  10.                   - Initial release 
  11.   Date Modified:    ??? 
  12.                   - ??? 
  13.   
  14.                   https://www.zoho.com/people/api/compensation/fetch_salary.html 
  15.                   https://www.zoho.com/analytics/api/v2/bulk-api/import-data/existing-table.html 
  16.   ******************************************************************************* */ 
  17.  // 
  18.  // init 
  19.  v_StartIndex = 1; 
  20.  v_CountTotal = 0; 
  21.  l_CsvRows = List(); 
  22.  l_ColumnsData = List(); 
  23.  l_Pages = {1,2,3,4,5,6,7}; 
  24.  // 
  25.  v_Headings = "ID,Employee ID,Effective From,Currency,Amount Per Annum,Created Time,Modified Time,Created By,Modified By,Pay Schedule"; 
  26.  l_CsvRows.add(v_Headings.toList().toString()); 
  27.  // 
  28.  // the zoho analytics org ID noted earlier 
  29.  v_AnalyticsOrgID = "123456879"; 
  30.  // 
  31.  // in analytics, browse to the target table and note the URL IDs after workspace and view 
  32.  v_WorkspaceID = "1234567000008912345"; 
  33.  v_ViewID = "9876543000002198765"; 
  34.  // 
  35.  // loop through all employees, this will do 1400 
  36.  for each  v_Page in l_Pages 
  37.  { 
  38.      // 
  39.      // query goals endpoint 
  40.      v_Endpoint_Salary = "https://people.zoho.com/api/compensation/v1/salary?mode=all&startIndex=" + v_StartIndex; 
  41.      r_SalaryData = invokeurl 
  42.      [ 
  43.          url :v_Endpoint_Salary 
  44.          type :GET 
  45.          connection:"zpeople" 
  46.      ]; 
  47.      //info r_SalaryData; 
  48.      // 
  49.      // parse the response loop through each record returned 
  50.      m_SalaryResponse = ifnull(r_SalaryData.get("response"),Map()); 
  51.      l_SalaryRecords = ifnull(m_SalaryResponse.get("result"),List()); 
  52.      for each  m_SalaryRecord in l_SalaryRecords 
  53.      { 
  54.          // 
  55.          // start an increment for counting the records processed 
  56.          v_CountTotal = v_CountTotal + 1; 
  57.          v_StartIndex = v_StartIndex + 1; 
  58.          l_CsvRow = List(); 
  59.          m_ColumnsData = Map(); 
  60.          // 
  61.          // v2: file 
  62.          l_CsvRow.add("\"" + m_SalaryRecord.get("Salary_Id") + "\""); 
  63.          l_CsvRow.add("\"" + m_SalaryRecord.get("Erecno") + "\""); 
  64.          // 
  65.          // date transform 
  66.          v_DateTransform = ifnull(m_SalaryRecord.get("Effective_From"),zoho.currentdate.toString("dd-MMM-yyyy")); 
  67.          m_DateMonthMap = {"Jan":"01","Feb":"02","Mar":"03","Apr":"04","May":"05","Jun":"06","Jul":"07","Aug":"08","Sep":"09","Oct":"10","Nov":"11","Dec":"12"}; 
  68.          l_DateEffectiveFromParts = v_DateTransform.toList("-"); 
  69.          v_SqlDate = if(l_DateEffectiveFromParts.size() > 1,l_DateEffectiveFromParts.get(2) + "-" + m_DateMonthMap.get(l_DateEffectiveFromParts.get(1)) + "-" + l_DateEffectiveFromParts.get(0),null); 
  70.          l_CsvRow.add(v_SqlDate); 
  71.          // 
  72.          // other fields 
  73.          l_CsvRow.add(m_SalaryRecord.get("Currency")); 
  74.          l_CsvRow.add(m_SalaryRecord.get("CTC")); 
  75.          l_CsvRow.add(zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss")); 
  76.          l_CsvRow.add(zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss")); 
  77.          l_CsvRow.add(null); 
  78.          l_CsvRow.add(null); 
  79.          l_CsvRow.add(m_SalaryRecord.get("Pay_Schedule")); 
  80.          l_CsvRows.add(l_CsvRow.toString()); 
  81.          // 
  82.          // v2: data 
  83.          m_ColumnsData.put("ID",m_SalaryRecord.get("Salary_Id")); 
  84.          m_ColumnsData.put("Employee ID",m_SalaryRecord.get("Salary_Id")); 
  85.          m_ColumnsData.put("Effective From",v_SqlDate); 
  86.          m_ColumnsData.put("Currency",m_SalaryRecord.get("Currency")); 
  87.          m_ColumnsData.put("Amount Per Annum",m_SalaryRecord.get("CTC")); 
  88.          m_ColumnsData.put("Created Time",zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss")); 
  89.          m_ColumnsData.put("Modified Time",zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss")); 
  90.          m_ColumnsData.put("Pay Schedule",m_SalaryRecord.get("Pay_Schedule")); 
  91.          l_ColumnsData.add(m_ColumnsData); 
  92.      } 
  93.  } 
  94.  // 
  95.  // 
  96.  // generate a CSV 
  97.  v_NewLine = hexToText("0A"); 
  98.  f_SalariesCSV = l_CsvRows.toString(v_NewLine).toFile("Salaries.csv"); 
  99.  f_SalariesCSV.setParamName("file"); 
  100.  /* 
  101.  m_Config.put("dateFormat","yyyy-MM-dd"); 
  102.  m_Config.put("onError","setcolumnempty"); 
  103.  m_Config.put("skipTop",1); 
  104.  */ 
  105.  //v_Params = "CONFIG=" + zoho.encryption.urlEncode(m_Config.toString()); 
  106.  // 
  107.  // 
  108.  /* 
  109.  // COMMON PARAMS 
  110.  m_Params = Map(); 
  111.  m_Params.put("ZOHO_ACTION","IMPORT"); 
  112.  m_Params.put("ZOHO_OUTPUT_FORMAT","JSON"); 
  113.  m_Params.put("ZOHO_ERROR_FORMAT","JSON"); 
  114.  m_Params.put("ZOHO_API_VERSION","1.0"); 
  115.  // ACTION SPECIFIC PARAMS 
  116.  m_Params.put("ZOHO_IMPORT_TYPE","UPDATEADD"); 
  117.  m_Params.put("ZOHO_AUTO_IDENTIFY","TRUE"); 
  118.  m_Params.put("ZOHO_ON_IMPORT_ERROR","SETCOLUMNEMPTY"); 
  119.  m_Params.put("ZOHO_CREATE_TABLE","FALSE"); 
  120.  // 
  121.  // 
  122.  */ 
  123.  /* 
  124.  r_SalariesCSV = invokeurl 
  125.  [ 
  126.      url: "https://test.com/tmp/salaries.csv" 
  127.      type: GET 
  128.  ]; 
  129.  f_SalariesCSV = r_SalariesCSV.toFile("Salaries.csv"); 
  130.  f_SalariesCSV.setParamName("file"); 
  131.  // 
  132.  v_QueryParam = ""; 
  133.  l_Keys = m_Params.keys(); 
  134.  for each  v_Key in l_Keys 
  135.  { 
  136.      v_QueryParam = v_QueryParam + "&" + v_Key + "=" + m_Params.get(v_Key); 
  137.  } 
  138.  // 
  139.  */ 
  140.  // 
  141.  // 
  142.  /* 
  143.  v_OwnerEmail = zoho.encryption.urlEncode("the_super_admin_email"); 
  144.  v_WorkspaceName = zoho.encryption.urlEncode("Zoho People Analytics"); 
  145.  v_ViewName = zoho.encryption.urlEncode("Test"); 
  146.  v_Endpoint_v1 = "https://analyticsapi.zoho.com/api/" + v_OwnerEmail + "/" + v_WorkspaceName + "/" + v_ViewName + "?" + v_QueryParam.getSuffix("&"); 
  147.  //info v_Endpoint_v1; 
  148.  */ 
  149.  // 
  150.  // authorisation header 
  151.  m_Header = Map(); 
  152.  //m_Header.put("Content-Type","multipart/form-data"); 
  153.  m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID); 
  154.  //info m_Header; 
  155.  // 
  156.  // v2 config 
  157.  m_Config = Map(); 
  158.  m_Config.put("importType","updateadd"); 
  159.  m_Config.put("fileType","json"); 
  160.  m_Config.put("autoIdentify","true"); 
  161.  // update on key 
  162.  m_Config.put("matchingColumns",{"ID"}); 
  163.  // 
  164.  // v2 data 
  165.  m_Params = Map(); 
  166.  m_Params.put("CONFIG",m_Config.toString()); 
  167.  m_Params.put("DATA",l_ColumnsData); 
  168.  // 
  169.  v_Endpoint_v2 = "https://analyticsapi.zoho.com/restapi/v2/workspaces/" + v_WorkspaceID + "/views/" + v_ViewID + "/data"; 
  170.  info v_Endpoint_v2; 
  171.  // 
  172.  r_FileUpload = invokeurl 
  173.  [ 
  174.      url :v_Endpoint_v2 
  175.      type :POST 
  176.      parameters:m_Params 
  177.      headers:m_Header 
  178.      connection:"zanalytics" 
  179.  ]; 
  180.  info r_FileUpload; 
  181.  // 
  182.  sendmail 
  183.  [ 
  184.      from :zoho.adminuserid 
  185.      to :"moi" 
  186.      subject :"DEBUGGING - Salary File - TO BE PERMANENTLY DELETED" 
  187.      message :m_Config 
  188.      Attachments :file:f_SalariesCSV 
  189.  ] 
  190.  // 
  191.  info "Processed " + v_CountTotal + " record(s)"; 


Category: Zoho People :: Article: 1724

Recent Posts

Joes Word Cloud

Accreditation

Badge - Zoho Creator Certified Developer Associate
Badge - Zoho Deluge Certified Developer
Badge - Certified Zoho CRM Developer

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com