This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Log in to ask a question
Log in to ask a question

Using Appscripts API and having a parent child relations

Posted on 01-24-2024 10:06 AM
Swv50
Bronze 3
Bronze 3
Reply posted on --/--/---- --:-- AM

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. 

0 9 131
Topic Labels
0 Likes
9 REPLIES 9

Posted on --/--/---- --:-- AM
Markus_Malessa
Gold 1
Gold 1
Reply posted on --/--/---- --:-- AM

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.

0 Likes

Posted on --/--/---- --:-- AM
Swv50
Bronze 3
Bronze 3
Reply posted on --/--/---- --:-- AM

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"}]}}]}}}

0 Likes

Posted on --/--/---- --:-- AM
Markus_Malessa
Gold 1
Gold 1
In response to Swv50
Reply posted on --/--/---- --:-- AM

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.

0 Likes

Posted on --/--/---- --:-- AM
Swv50
Bronze 3
Bronze 3
In response to Markus_Malessa
Reply posted on --/--/---- --:-- AM

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

0 Likes

Posted on --/--/---- --:-- AM
Markus_Malessa
Gold 1
Gold 1
Reply posted on --/--/---- --:-- AM

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.

0 Likes

Posted on --/--/---- --:-- AM
Swv50
Bronze 3
Bronze 3
In response to Markus_Malessa
Reply posted on --/--/---- --:-- AM

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

}

'

 

0 Likes

Posted on --/--/---- --:-- AM
Markus_Malessa
Gold 1
Gold 1
In response to Swv50
Reply posted on --/--/---- --:-- AM

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.

0 Likes

Posted on --/--/---- --:-- AM
Swv50
Bronze 3
Bronze 3
In response to Markus_Malessa
Reply posted on --/--/---- --:-- AM

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

0 Likes

Posted on --/--/---- --:-- AM
Markus_Malessa
Gold 1
Gold 1
Reply posted on --/--/---- --:-- AM

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
0 Likes
Top Labels in this Space