You need to sign in to do that
Don't have an account?
澄人 鈴木 (Sumito)
Returned "Undefined" when using Google apps script to downloading Account data from Opportunity
Returned "Undefined" when using Google apps script to downloading Account data from Opportunity. Please help me to get proper record from Field "Account.Name".
Copied following method.
https://gist.github.com/stomita/990589
Modified as followings:
from: Opportunity
fields: Id,Account.Name
----
// Query account data from Salesforce, using REST API with OAuth2 access token.
var fields = "Id,Account.Name";
var soql = "SELECT "+fields+" FROM Opportunity LIMIT 100";
var queryUrl = instanceUrl + "/services/data/v21.0/query?q="+encodeURIComponent(soql);
var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+accessToken } });
var queryResult = Utilities.jsonParse(response.getContentText());
----
Copied following method.
https://gist.github.com/stomita/990589
Modified as followings:
from: Opportunity
fields: Id,Account.Name
----
// Query account data from Salesforce, using REST API with OAuth2 access token.
var fields = "Id,Account.Name";
var soql = "SELECT "+fields+" FROM Opportunity LIMIT 100";
var queryUrl = instanceUrl + "/services/data/v21.0/query?q="+encodeURIComponent(soql);
var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+accessToken } });
var queryResult = Utilities.jsonParse(response.getContentText());
----
You are referencing parent field (Account.Name) and the script just uses the root value of the record by its field path when rendering spread sheet.
```
// Render result records into cells
queryResult.records.forEach(function(record, i) {
fields.forEach(function(field, j) { cell.offset(i+1, j).setValue(record[field]) });
});
```
You should access nested property (Account.Name) properly in the JSON record.
Since I am not engineer, need code-base help to complete this issue.
If you have any specific idea for above, please let me know.
Have you got any reply ?
I had the same problem that was reported in this post. I forked the github code and updated the function makeRequestSoql so that I no longer get the error of "undefined". https://github.com/joyhanawa/sfconnect
1. I simplified the loops (removing one)
2. I renamed the variables so they made more sense to me.
3. I made the substring case insensitive since my SOQL was not perfectly formed, others might have that same issue.
The change to the code is:
// SOQL version of makeRequest()
// The try-catch can be removed if you do not need to track errors
function makeRequestSoql(soql, sheetName) {
var sfService = getSfService();
var userProps = PropertiesService.getUserProperties();
var props = userProps.getProperties();
var name = getSfService().serviceName_;
var obj = JSON.parse(props['oauth2.' + name]);
var instanceUrl = obj.instance_url;
var queryUrl = instanceUrl + "/services/data/v47.0/query?q="+encodeURIComponent(soql); // Actual request for report Data
var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+sfService.getAccessToken() } });
var queryResult = JSON.parse(response.getContentText());
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName(sheetName);
var answer = queryResult.records; // assumes tabular report
var fields = soql.substring(7, soql.toUpperCase().indexOf('FROM')-1);
var headers = fields.split(",");
var myArray = [headers];
for (i = 0 ; i < answer.length ; i++ ) {
var tempArray = [];
for (j = 0 ; j < headers.length ; j++) {
try {
var varName="";
var valuePair = [];
var varName=headers[j].trim(); //some headers have spaces
var valuePair = answer[i];
var varValue = valuePair[varName];
tempArray.push(varValue);
}
catch(e) {
varValue = "";
break;
}
}
myArray.push(tempArray);
}
var lastRow = sheet.getLastRow();
if (lastRow < 1) lastRow = 1;
sheet.getRange(1,1,lastRow, myArray[0].length).clearContent();
sheet.getRange(1,1, myArray.length, myArray[0].length).setValues(myArray);
}