After two eight-hour days of Googling and trying various bits of code, I'm here, asking for your help.
Can anyone please help me understand what this error means?
TypeError: Cannot read properties of undefined (reading 'forEach')
Below is the Google Apps Script I'm running which never returns any data to my Google Sheet, only the TypeError above.
My end goal is so extremely simple: I need to take the data returned by the API and simply parse it into cells in a Google Sheet. I'm certain that the API is responsive as I've been able to see a "200" code (which indicates success) in the log as well as seeing the actual data I need to parse in its raw format in the log (when I rewrite the code to show the data in the log). The most I can tell is that there is something wrong with the forEach loop.
function myFunction() {
var url = "https://example.com";
var params = {
"headers": {
"Authorization": 'Basic ' + Utilities.base64Encode(Username + ':' + Password),
"Act-Database-Name": "Sample"
}
};
var response = UrlFetchApp.fetch(url, params);
var code = response.getResponseCode();
var token = response.getContentText();
Logger.log(code);
Logger.log(token);
var params2 = {
"headers": { "Authorization": 'Bearer ' + token }
};
try {
// call the API
var url2 = "https://example.com/api/opportunities";
var jsondata = UrlFetchApp.fetch(url2, params2);
var code2 = jsondata.getResponseCode();
Logger.log(code2);
var data = jsondata.getContentText();
var json = JSON.parse(data);
// get data array
var arrayData = json['opportunities']; // replace 'data' with the name of your 1st-level array
// blank array to hold the data for Sheet
var arrayProperties = [];
// Add the arrayProperties to the array
arrayData.forEach(function (el) {
arrayProperties.push([el.status,
el.opportunityName,
el.contact,
el.probabilityofClose,
el.weightedTotal,
el.total,
el.editDate,
el.estimatedCloseDate,
el.actualCloseDate,
el.recordManager,
el.leadTime,
el.accessLevel,
el.associatedWith,
el.company,
el.competitor,
el.createDate,
el.daysinStage,
el.daysOpen,
el.grossMargin,
el.importDate,
el.lastEditedBy,
el.openDate,
el.opportunityField2,
el.opportunityField3,
el.opportunityField4,
el.opportunityField5,
el.opportunityField6,
el.opportunityField7,
el.opportunityField8,
el.private,
el.process,
el.product,
el.reason,
el.recordCreator,
el.referredBy,
el.sourceid,
el.stage,
el.stageStartDate]
);
});
// select the output sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Current'); // replace 'Sheet1' with the name of your Sheet
// calculate the number of rows and columns needed
var numRows = arrayProperties.length;
var numCols = arrayProperties[0].length;
// output the numbers to the sheet
sheet.getRange(2, 1, numRows, numCols).setValues(arrayProperties.reverse())
}
catch (error) {
// deal with any errors
Logger.log(error);
};
}
According to the API documentation, the data should look like this:
[
{
"id": "00000000-0000-0000-0000-000000000000",
"name": "string",
"actualCloseDate": "2022-12-07T18:10:03.367Z",
"companies": [
{
"id": "00000000-0000-0000-0000-000000000000",
"name": "string"
}
],
"competitor": "string",
"contactNames": "string",
"contacts": [
{
"id": "00000000-0000-0000-0000-000000000000",
"displayName": "string",
"company": "string"
}
],
"creator": "string",
"daysOpen": 0,
"daysInStage": 0,
"estimatedCloseDate": "2022-12-07T18:10:03.367Z",
"stageStartDate": "2022-12-07T18:10:03.367Z",
"grossMargin": 0,
"groups": [
{
"id": "00000000-0000-0000-0000-000000000000",
"name": "string"
}
],
"isPrivate": true,
"manager": "string",
"openDate": "2022-12-07T18:10:03.367Z",
"probability": 0,
"productTotal": 0,
"reason": "string",
"source": "string",
"sourceId": "00000000-0000-0000-0000-000000000000",
"stage": {
"id": "00000000-0000-0000-0000-000000000000",
"name": "string",
"description": "string",
"probability": 0,
"number": 0,
"process": {
"id": "00000000-0000-0000-0000-000000000000",
"name": "string",
"description": "string",
"status": "None",
"stagesCount": 0,
"stages": [
{}
]
}
},
"status": "Open",
"importDate": "2022-12-07T18:10:03.367Z",
"totalPerCompany": 0,
"totalPerContact": 0,
"totalPerGroup": 0,
"weightedTotal": 0,
"quickbooksInvoiceId": "string",
"relatedEntitiesResolver": true,
"recordManagerID": "00000000-0000-0000-0000-000000000000",
"customFields": {},
"created": "2022-12-07T18:10:03.367Z",
"edited": "2022-12-07T18:10:03.367Z",
"editedBy": "string",
"recordOwner": "string",
"recordManager": "string"
}
]
The API documentation also shows a "Model" that looks like this:
Inline Model [
act.web.api.models.Opportunities.Opportunity
]
act.web.api.models.Opportunities.Opportunity {
id (string, optional): A unique identifier that represents a potential piece of business. ,
name (string, optional): The name of an opportunity. ,
actualCloseDate (string, optional): The date that the opportunity as closed. ,
companies (Array[act.web.api.models.Companies.EmbeddedCompany], optional): List of companies that are associated to this opportunity entity. ,
competitor (string, optional): Gets the name of the competitor for this opportunity. ,
contactNames (string, optional): The names of the contacts associated with this opportunity. ,
contacts (Array[act.web.api.models.Contacts.EmbeddedContact], optional): List of contacts that are associated to this opportunity entity. ,
creator (string, optional): The name of the creator of this opportunity. ,
daysOpen (integer, optional): The number of days this opportunity has been opened. ,
daysInStage (integer, optional): the number of days this opportunity has been in a given stage. ,
estimatedCloseDate (string, optional): The date the opportunity is expected to be closed. ,
stageStartDate (string, optional): The date the opportunity stage started. ,
grossMargin (number, optional): Represents the net sales less the cost of goods and services sold. ,
groups (Array[act.web.api.models.Groups.EmbeddedGroup], optional): List of groups that are associated to this opportunity entity. ,
isPrivate (boolean, optional): Indicates whether this opportunity is private ,
manager (string, optional): The name of the record manager of this opportunity. ,
openDate (string, optional): The date the opportunity was opened. ,
probability (integer, optional): The likelihood this opportunity will will be won by the close date. ,
productTotal (number, optional): The total cost of the product or services. ,
reason (string, optional): Describes why the opportunity status changed. ,
source (string, optional): Describes the source of the opportunity. ,
sourceId (string, optional): The external source id for the opportunity ,
stage (act.web.api.models.Opportunities.Stage, optional): The stage of the opportunity. ,
status (string, optional): This displays the status of the opportunity (Open, Won, Lost, or inactive). = ['Open', 'Won', 'Lost', 'Inactive'],
importDate (string, optional): This readonly property displays the date that the opportunity was imported into the system. ,
totalPerCompany (number, optional): The average total per associated company. ,
totalPerContact (number, optional): The average total per associated contact. ,
totalPerGroup (number, optional): The average total per associated group. ,
weightedTotal (number, optional): The weight of the product. ,
quickbooksInvoiceId (string, optional): Quickbooks external Id to match record with quickbooks contact ,
relatedEntitiesResolver (boolean, optional): If true: prevents related entities from serializing. ,
recordManagerID (string, optional): The record manager. ,
customFields (object, optional),
created (string, optional),
edited (string, optional),
editedBy (string, optional),
recordOwner (string, optional),
recordManager (string, optional)
}
act.web.api.models.Companies.EmbeddedCompany {
id (string, optional): Gets a unique identifier (id) for a given company. ,
name (string, optional): Gets the name of the company.
}
act.web.api.models.Contacts.EmbeddedContact {
id (string, optional): ,
displayName (string, optional, read only): ,
company (string, optional):
}
act.web.api.models.Groups.EmbeddedGroup {
id (string, optional): Gets a unique identifier (id) for a given group. ,
name (string, optional): Gets the name of the group.
}
act.web.api.models.Opportunities.Stage {
id (string, optional): Gets the unique identifier of the stage. ,
name (string, optional): Gets/Sets the name of the stage. ,
description (string, optional): Gets/Sets the description of the stage. ,
probability (integer, optional): Gets/Sets the probability of the stage. ,
number (integer, optional): The order of the stage progresses throw the process lifecycle. ,
process (act.web.api.models.Opportunities.Process, optional): Represents the current process for a given opportunity
}
act.web.api.models.Opportunities.Process {
id (string, optional): Gets the unique identifier of the process. ,
name (string, optional): The name of the process. ,
description (string, optional): The description of the process. ,
status (string, optional): The description of the process. = ['None', 'Active', 'Inactive'],
stagesCount (integer, optional): Count of stages of current process ,
stages (Array[act.web.api.models.Opportunities.Stage], optional): Customized stages for the new process
}
Thank you very much in advance ❤️.
