Using Appscripts API and having a parent child relations

Swv50 · January 24, 2024, 6:06pm

I am using appscripts to Post and get data. I will Post data and receive a variable response that i need to split the data inside the response to have separate rows for the received data.

On AppScript we are getting [Arrary of results]

To understand the use case i am sending a Vehicle Identification number (VIN) to receive Part Information. and i could have multiple parts in the response that needs to be separated by rows.

i know i can do this with google sheets but i am using appsheet database for faster data write/read speeds along with live changes for all devices on updates without needed to manually sync the device.

I have an Enterprise account and 200,000 rows is adequate for the time being.

all help is appreciated.

Markus_Malessa · January 24, 2024, 7:56pm

Where are you trying to split the data from the response? Purely from Appscript or within Appsheet directly? If you are just searching for matching parts based on VIN wouldn’t the search functionality within Appsheet work better? I would agree that writing records using the API is possibly more efficient, but searching you would need something where to parse the response to.

Swv50 · January 24, 2024, 11:14pm

I am collecting parts from an api call inside of a shopping cart. and i have to split the parts into separate rows to add them to appsheet via api.

this is the example data we are recieving:

{“Response”:{“RequestType”:“Cart”,“Success”:“1”,“ErrorCount”:“0”,“Header”:{“AccountId”:“SourceIt”,“CustomerName”:“Value”,“OrderTotal”:“108.11”,“Identifier”:“42345”},“Cart”:{“BranchCart”:

Row 1 [{“Name”:“value”,“Id”:“2”,“BranchId”:“3535038”,“SellerId”:“Value”,“VendorUsername”:“Value”,“VendorName”:“value)”,“Total”:“63.81”,“ListTotal”:“94.44”,“ItemCount”:“1”,“Parts”:{“Part”:[{“PartNumber”:“A3686C”,“LineCode”:“ACD”,“SellerPartDesc”:“Air Filter”,“CatalogPartDesc”:“Air Filter”,“CatalogFootNotes”:“Years: 2015 - 2019; Per Veh: 1;”,“MfgCode”:“DCC”,“SmartpageURL”:“Value”,“OrderQty”:“1”,“AvailableQty”:“71”,“Mdse”:“63.81”,“List”:“94.44”,“Core”:“0.00”,“Uom”:“ea”,“PackQty”:“1”,“PartType”:“6192”,“VehicleId”:“136657”,“WHIEngineConfigId”:“19828”,“VehicleDesc”:“2016 Lexus NX300h Base 2.5L L4”}]}},

Row 2

{“Name”:“value”,“Id”:“1”,“BranchId”:“3535037”,“SellerId”:“Value”,“VendorUsername”:“Value”,“VendorName”:“Value)”,“Total”:“44.30”,“ListTotal”:“65.56”,“ItemCount”:“1”,“Parts”:{“Part”:[{“PartNumber”:“CF3320C”,“LineCode”:“ACD”,“SellerPartDesc”:“Cabin Air Filter”,“CatalogPartDesc”:“Cabin Air Filter”,“CatalogFootNotes”:“Years: 2015 - 2019; Per Veh: 1; Carbon;”,“MfgCode”:“DCC”,“SmartpageURL”:“Value”,“OrderQty”:“1”,“AvailableQty”:“5”,“Mdse”:“44.30”,“List”:“65.56”,“Core”:“0.00”,“Uom”:“ea”,“PackQty”:“1”,“PartType”:“6832”,“VehicleId”:“136657”,“WHIEngineConfigId”:“19828”,“VehicleDesc”:“2016 Lexus NX300h Base 2.5L L4”}]}}]}}}

Markus_Malessa · January 24, 2024, 11:28pm

Ok, so which part of the Response are you trying to get to? I assume the “BranchCart”: under the “Cart”: part? You want to post the code that you have tried? I suspect you are having troubles declaring variables and/or parsing the JSON content that then would allow you to loop over the items to add to Appsheet? I suppose looking at your example JSON response you may even need to get down to the Parts>Part? Once your JSON is parsed you should be able to use a declaration similar to Response.Header.Cart.BranchCart[0].Parts.Part which should get you to the array with the part numbers, then loop over those to add to Appsheet via the API. If that is what you are after, and assuming the JSON response is consistent.

Swv50 · January 25, 2024, 12:24am

Yes i am having an issue with defining the objects and separating the objects as different rows. here is the code i am currently using:

’ function getCartContents(
Rev, ClientVersion, RequestType, PartnerProviderKey, ApiTokenKey, Identifier, url) {
let url = url;

var payload = {
“Request”: {
“Rev”: Rev,
“ClientVersion”: ClientVersion,
“RequestType”: RequestType,
“ApiTokenKey”: ApiTokenKey,
“Identifier”: Identifier,
“PartnerProviderKey”: PartnerProviderKey,
“BranchCart”: [
{
“BranchId”: Identifier,
“OrderHeader”: {

},
}
]
},

};

var options = {
‘method’: ‘post’,
‘contentType’: ‘application/json’,
‘payload’: JSON.stringify(payload)
};

try {
let response = UrlFetchApp.fetch(url, options);
let responseData = JSON.parse(response.getContentText());

if (responseData.Response && responseData.Response.Success === “1”) {
// Extract Header details
let header = responseData.Response.Header;
if (responseData.Response.Cart && responseData.Response.Cart.BranchCart) {
responseData.Response.Cart.BranchCart.forEach((branchCart) => {
// Extract BranchCart details
let branchCartDetails = [
branchCart.Name, branchCart.Id, branchCart.BranchId, branchCart.SellerId, branchCart.VendorUsername, branchCart.Total,
branchCart.ListTotal, branchCart.ItemCount, branchCart.OrderType,
branchCart.OrderTypeDesc, branchCart.DeliveryMethod, branchCart.DeliveryMethodDesc
];

if (branchCart.Parts && branchCart.Parts.Part) {
let parts = Array.isArray(branchCart.Parts.Part) ? branchCart.Parts.Part : [branchCart.Parts.Part];
parts.forEach(part => {
let partRow = [
// Header details
header.AccountId, header.CustomerName, header.OrderTotal, header.Identifier,
// BranchCart and Part details
…branchCartDetails,
part.PartNumber, part.LineCode, part.AvailableQty, part.SellerPartDesc,
part.CatalogPartDesc, part.MfgCode, part.OrderQty, part.PartIdentifier,
part.Mdse, part.List, part.Core, part.PartType, part.VehicleId,
part.WHIEngineConfigId, part.SubmodelId, part.VehicleDesc,
part.CatalogFootNotes,
part.Comment, part.Comments, part.ErrorCode, part.Id,
part.ItemCount, part.OrderType, part.ListTotal, part.OrderDetails,
part.OrderNumber, part.PackQty, part.Part, part.SellerId, part.ShipQty,
part.SmartpageURL, part.Total, part.Type,
part.Uom
];
return partRow;
});
}
});
} else {
Logger.log(“Cart or BranchCart data not available in the response.”);
}
} else {
Logger.log(“Response indicates failure or Success != 1”);
}
} catch (e) {
Logger.log("Error in script: " + e.toString());
}

let response = UrlFetchApp.fetch(url, options);
Logger.log(response.getContentText()); ’

Markus_Malessa · January 25, 2024, 1:28am

Did you end up finding your own solution? It looked like you put up your code but then took it back down. From what I saw for a moment I thought that your if(responseData.Cart && responseData.Cart.CartBranch) might not evaluate to true simply because I don’t recall offhand if it needs a comparison or not like !== undefined or !== null. To test some of this I would add that in a console.log() just prior to the if to see if it returns as true or false.

Swv50 · January 25, 2024, 2:11am

no i havnt figured it out yet. the code should still be up if not here is the most recent:

’

function getCartContents(Rev, ClientVersion, RequestType, PartnerProviderKey, ApiTokenKey, Identifier, url) {
let url = url;

var payload = {
“Request”: {
“Rev”: Rev,
“ClientVersion”: ClientVersion,
“RequestType”: RequestType,
“ApiTokenKey”: ApiTokenKey,
“PartnerProviderKey”: PartnerProviderKey,
},
};
Logger.log(payload);

var options = {
‘method’: ‘post’,
‘contentType’: ‘application/json’,
‘payload’: JSON.stringify(payload)
};

try {
let response = UrlFetchApp.fetch(url, options);
let data = JSON.parse(response.getContentText());

Logger.log("Full Response: " + response.getContentText());

if (data.Response && data.Response.Success === “1”) {
let Header = data.Response.Header;
let BranchCarts = data.Response.Cart.BranchCart; // This is now an array

Logger.log("Header: " + JSON.stringify(Header));
Logger.log("BranchCarts: " + JSON.stringify(BranchCarts));

let allLogs = {
“Header”: {
“AccountId”: Header.AccountId,
“CustomerName”:Header.CustomerName,
“OrderTotal”: Header.OrderTotal,
“Identifier”:Header.Identifier,
},
“BranchCarts”: BranchCarts.map(cart => ({
“Name”: cart.Name,
“Id”: cart.Id
}))

};
Logger.log("All Logs: " + JSON.stringify(allLogs));
return allLogs;
}
} catch (e) {
Logger.log("Error: " + e.toString());
}

}

’

Markus_Malessa · January 25, 2024, 2:16am

Feel free to private message me the code. I can take a look at it in the morning, about 12 hours from now. I’d just paste it directly into Apps Script so it’s properly formatted.

Swv50 · January 25, 2024, 2:29am

So the main issue is that the branch cart is an array and i cant pass the objects related to the branchcart

Markus_Malessa · January 25, 2024, 2:35pm

I edited your script a little to make it work for a testing environment. With those edits I was able to write things to the console, however I did observe that a lot of your branchCart definitions and part definitions returned undefined. I posted the edited code in one block below and the console log in another block. I guess from here I was wondering what is not working for you, as your code spits something out and I can only assume that what is returned in this function does not play well with the next step?

function getCartContents1() {
  try {
    let response = JSON.stringify({"Response":{"RequestType":"Cart","Success":"1","ErrorCount":"0","Header":{"AccountId":"SourceIt","CustomerName":"Value","OrderTotal":"108.11","Identifier":"42345"},"Cart":{"BranchCart":[{"Name":"value","Id":"2","BranchId":"3535038","SellerId":"Value","VendorUsername":"Value","VendorName":"value)","Total":"63.81","ListTotal":"94.44","ItemCount":"1","Parts":{"Part":[{"PartNumber":"A3686C","LineCode":"ACD","SellerPartDesc":"Air Filter","CatalogPartDesc":"Air Filter","CatalogFootNotes":"Years: 2015 - 2019; Per Veh: 1;","MfgCode":"DCC","SmartpageURL":"Value","OrderQty":"1","AvailableQty":"71","Mdse":"63.81","List":"94.44","Core":"0.00","Uom":"ea","PackQty":"1","PartType":"6192","VehicleId":"136657","WHIEngineConfigId":"19828","VehicleDesc":"2016 Lexus NX300h Base 2.5L L4"}]}},{"Name":"value","Id":"1","BranchId":"3535037","SellerId":"Value","VendorUsername":"Value","VendorName":"Value)","Total":"44.30","ListTotal":"65.56","ItemCount":"1","Parts":{"Part":[{"PartNumber":"CF3320C","LineCode":"ACD","SellerPartDesc":"Cabin Air Filter","CatalogPartDesc":"Cabin Air Filter","CatalogFootNotes":"Years: 2015 - 2019; Per Veh: 1; Carbon;","MfgCode":"DCC","SmartpageURL":"Value","OrderQty":"1","AvailableQty":"5","Mdse":"44.30","List":"65.56","Core":"0.00","Uom":"ea","PackQty":"1","PartType":"6832","VehicleId":"136657","WHIEngineConfigId":"19828","VehicleDesc":"2016 Lexus NX300h Base 2.5L L4"}]}}]}}});

    let responseData = JSON.parse(response);
    
    if (responseData.Response && responseData.Response.Success === "1") {
      // Extract Header details
      let header = responseData.Response.Header;
      if (responseData.Response.Cart && responseData.Response.Cart.BranchCart) {
        responseData.Response.Cart.BranchCart.forEach((branchCart) => {
          // Extract BranchCart details
          let branchCartDetails = [
            branchCart.Name, branchCart.Id, branchCart.BranchId, branchCart.SellerId, branchCart.VendorUsername, branchCart.Total, 
            branchCart.ListTotal, branchCart.ItemCount, branchCart.OrderType, 
            branchCart.OrderTypeDesc, branchCart.DeliveryMethod, branchCart.DeliveryMethodDesc
          ];
          console.log(branchCartDetails);
 
          if (branchCart.Parts && branchCart.Parts.Part) {
            let parts = Array.isArray(branchCart.Parts.Part) ? branchCart.Parts.Part : [branchCart.Parts.Part];
            console.log(parts);
            parts.forEach(part => {
              let partRow = [
                // Header details
                header.AccountId, header.CustomerName, header.OrderTotal, header.Identifier,
                // BranchCart and Part details
                ...branchCartDetails,
                part.PartNumber, part.LineCode, part.AvailableQty, part.SellerPartDesc, 
                part.CatalogPartDesc, part.MfgCode, part.OrderQty, part.PartIdentifier,
                part.Mdse, part.List, part.Core, part.PartType, part.VehicleId, 
                part.WHIEngineConfigId, part.SubmodelId, part.VehicleDesc,
                part.CatalogFootNotes,
                part.Comment, part.Comments, part.ErrorCode, part.Id, 
                part.ItemCount, part.OrderType, part.ListTotal, part.OrderDetails, 
                part.OrderNumber, part.PackQty, part.Part, part.SellerId, part.ShipQty, 
                part.SmartpageURL, part.Total, part.Type, 
                part.Uom 
              ];
              console.log(partRow);
              return partRow;
            });
          }
        });
      } else {
        Logger.log("Cart or BranchCart data not available in the response.");
      }
    } else {
      Logger.log("Response indicates failure or Success != 1");
    }
  } catch (e) {
    Logger.log("Error in script: " + e.toString());
  }
}

7:34:28 AM	Notice	Execution started
7:34:29 AM	Info	[ 'value',
  '2',
  '3535038',
  'Value',
  'Value',
  '63.81',
  '94.44',
  '1',
  undefined,
  undefined,
  undefined,
  undefined ]
7:34:29 AM	Info	[ { PartNumber: 'A3686C',
    LineCode: 'ACD',
    SellerPartDesc: 'Air Filter',
    CatalogPartDesc: 'Air Filter',
    CatalogFootNotes: 'Years: 2015 - 2019; Per Veh: 1;',
    MfgCode: 'DCC',
    SmartpageURL: 'Value',
    OrderQty: '1',
    AvailableQty: '71',
    Mdse: '63.81',
    List: '94.44',
    Core: '0.00',
    Uom: 'ea',
    PackQty: '1',
    PartType: '6192',
    VehicleId: '136657',
    WHIEngineConfigId: '19828',
    VehicleDesc: '2016 Lexus NX300h Base 2.5L L4' } ]
7:34:29 AM	Info	[ 'SourceIt',
  'Value',
  '108.11',
  '42345',
  'value',
  '2',
  '3535038',
  'Value',
  'Value',
  '63.81',
  '94.44',
  '1',
  undefined,
  undefined,
  undefined,
  undefined,
  'A3686C',
  'ACD',
  '71',
  'Air Filter',
  'Air Filter',
  'DCC',
  '1',
  undefined,
  '63.81',
  '94.44',
  '0.00',
  '6192',
  '136657',
  '19828',
  undefined,
  '2016 Lexus NX300h Base 2.5L L4',
  'Years: 2015 - 2019; Per Veh: 1;',
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  '1',
  undefined,
  undefined,
  undefined,
  'Value',
  undefined,
  undefined,
  'ea' ]
7:34:29 AM	Info	[ 'value',
  '1',
  '3535037',
  'Value',
  'Value',
  '44.30',
  '65.56',
  '1',
  undefined,
  undefined,
  undefined,
  undefined ]
7:34:29 AM	Info	[ { PartNumber: 'CF3320C',
    LineCode: 'ACD',
    SellerPartDesc: 'Cabin Air Filter',
    CatalogPartDesc: 'Cabin Air Filter',
    CatalogFootNotes: 'Years: 2015 - 2019; Per Veh: 1; Carbon;',
    MfgCode: 'DCC',
    SmartpageURL: 'Value',
    OrderQty: '1',
    AvailableQty: '5',
    Mdse: '44.30',
    List: '65.56',
    Core: '0.00',
    Uom: 'ea',
    PackQty: '1',
    PartType: '6832',
    VehicleId: '136657',
    WHIEngineConfigId: '19828',
    VehicleDesc: '2016 Lexus NX300h Base 2.5L L4' } ]
7:34:29 AM	Info	[ 'SourceIt',
  'Value',
  '108.11',
  '42345',
  'value',
  '1',
  '3535037',
  'Value',
  'Value',
  '44.30',
  '65.56',
  '1',
  undefined,
  undefined,
  undefined,
  undefined,
  'CF3320C',
  'ACD',
  '5',
  'Cabin Air Filter',
  'Cabin Air Filter',
  'DCC',
  '1',
  undefined,
  '44.30',
  '65.56',
  '0.00',
  '6832',
  '136657',
  '19828',
  undefined,
  '2016 Lexus NX300h Base 2.5L L4',
  'Years: 2015 - 2019; Per Veh: 1; Carbon;',
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  '1',
  undefined,
  undefined,
  undefined,
  'Value',
  undefined,
  undefined,
  'ea' ]
7:34:29 AM	Notice	Execution completed

Topic		Replies	Views
doPost() from Google Apps Script - Passing Sheet Data into JSON AppSheet Q&A admin , automation	5	83	December 19, 2020
Google App Script - Appsheet API - write data to Spreadsheet \ Add fields name to row 1 AppSheet Q&A other	17	24	August 13, 2020
Hi there. I have two tables in an auditing ap... AppSheet Q&A admin	18	5	October 4, 2018

Using Appscripts API and having a parent child relations

AI Suggested topics