+ Start a Discussion
澄人 鈴木 (Sumito)澄人 鈴木 (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());
----

ShinShin

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.

澄人 鈴木 (Sumito)澄人 鈴木 (Sumito)
Thanks for your advice.

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.
Abdel BENDAOUAAbdel BENDAOUA
Sumito

Have you got any reply ?
 
JhanawaJhanawa
First, I want to thank appsscripted for his excellent work creating these scripts and publishing them.  They have been an amazing tool for learning this process.  https://github.com/appsscripted/sfconnect

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);

}