CODEWITHSUNDEEP
javascriptgoogle-apps-scriptzoho
Rafa Serrano
Rafa Serrano

Reputation: 23

Google Spreadsheet - Get records from Zoho

From, a google spreadshhet, I'm trying to collect records from Zoho.

I've tested this code:

var urlgetRecords = 'https://crm.zoho.com/crm/private/json/Leads/getRecords?newFormat=1&authtoken=00000&scope=crmapi&selectColumns=Leads(First Name,Last Name,Email)&fromIndex=1&toIndex=2&sortColumnString=First Name&sortOrderString=desc';

var jsonResponse = UrlFetchApp.fetch(urlgetgetRecords);

This is what obtain:

{"response":{"result":{"Leads":{"row":[{"no":"1","FL":[{"content":"1412559000000441145","val":"LEADID"},{"content":"Víctor","val":"First Name"},{"content":"Aguilera Moreno","val":"Last Name"},{"content":"[email protected]","val":"Email"}]},{"no":"2","FL":[{"content":"1412559000000308001","val":"LEADID"},{"content":"Victor","val":"First Name"},{"content":"Porta","val":"Last Name"},{"content":"[email protected]","val":"Email"}]}]}},"uri":"/crm/private/json/Leads/getRecords"}}

Please, How can write every row in this response in a spreadsheet?

Thank you very much in advance :)

Upvotes: 1

Views: 1079

Answers (2)

Rafa Serrano
Rafa Serrano

Reputation: 23

The Sandy Good's code is perfect. I've had to add two more lines.

Finally the code that works is:

var urlgetRecords = 'https://crm.zoho.com/crm/private/json/Leads/getRecords?newFormat=1&authtoken=' + authToken + '&scope=crmapi&selectColumns=Leads(First Name,Last Name,Email)&fromIndex=1&toIndex=2&sortColumnString=First Name&sortOrderString=desc';
var jsonResponse = UrlFetchApp.fetch(urlgetRecords);
 var jsonText = jsonResponse.getContentText();
 var objetoFinal = JSON.parse(jsonText);
 writeZohoToSheet(objetoFinal);

Thank you very much for your help @SandyGood

Upvotes: 1

Alan Wells
Alan Wells

Reputation: 31300

Write ZOHO data to Google spreadsheet. To use the code below, edit the sheet tab name in the getSheetByName('Sheet2') method to the sheet tab where you want the data to be appended to.

I've tested this code and it works:

function writeZohoToSheet(zohoDataObject) {
  var zohoDataObject = {"response":
                        {"result":
                         {"Leads":
                          {"row":
                           [{"no":"1","FL":[
                              {"content":"12345678IDNumber","val":"LEADID"},
                              {"content":"Víctor","val":"First Name"},
                              {"content":"Aguilera Moreno","val":"Last Name"},
                              {"content":"[email protected]","val":"Email"}]
                            },
                            {"no":"2","FL":[
                              {"content":"987654321IDNumber","val":"LEADID"},
                              {"content":"Victor","val":"First Name"},
                              {"content":"Porta","val":"Last Name"},
                              {"content":"[email protected]","val":"Email"}]
                            }
                           ]
                          }
                         },"uri":"/crm/private/json/Leads/getRecords"}};

  var rows = zohoDataObject.response.result.Leads.row;
  Logger.log('rows: ' + rows);
  Logger.log('rows.length: ' + rows.length);

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Sheet2');
  var array = [], thisRow, thisContent, innerObj;

  for (var r in rows) {
    array = []; //reset
    thisRow = rows[r];
    thisContent = thisRow.FL;

    array.push(r);

    for (var i=0;i<thisContent.length;i+=1) {
      innerObj = thisContent[i];
      array.push(innerObj.val);
      array.push(innerObj.content);
    };

    sh.appendRow(array);
  }; 
};

You would call the function with:

writeZohoToSheet(jsonResponse);

Remove the "hard coded" object literal, or comment it out in order to pass in the retrieved object.

Upvotes: 0

Related Questions