How to return specific data to cell in sheet-CodePudding

I'm currently creating a Google Apps Script for my company. We're a marketing agency in control of many Facebook ad accounts. Shortly, we have to update numbers on our own Google Sheet with spending from the clients ad accounts every day. I hated the robotic, manual labor, so I decided to come up with this solution.

The cell would contain the following formula:

=FacebookReporting("ad_acc_id","[{since:'2022-08-01',until:'2022-08-08'}]")

That way we could create these formulas for each of our ad accounts for whatever timeframe, and the numbers would update automatically every time we entered our sheet.

So far, my code looks like this:

function FacebookReporting(input1, input2) {
    
var AD_ACCOUNT_ID = input1
var TIME_RANGES = input2

// ad, adset, campaign, account
const LEVEL = 'account'

// https://developers.facebook.com/docs/marketing-api/insights/parameters#fields
const FIELDS = 'spend'

// Your user access token
const TOKEN = 'EAAORfG6N4U4BAONzrmHW18PDC0FCGgifwZCCPc692CiQ1UtGvdQoUb0usjdUpWV0cLe9g1sfeWu9XqGcVjexSWvMEkWTpFTqXjWb0skFL5ZCpknaGMZAi5WO9VNubgFL0556NHh2jqAhipZCI0zbOm8f57YCdnM3S7EXTLEZBtrFlJ7Ob0CIG'



  // Builds the Facebook Ads Insights API URL
  const facebookUrl = `https://graph.facebook.com/v14.0/act_${AD_ACCOUNT_ID}/insights?level=${LEVEL}&fields=${FIELDS}&time_ranges=${TIME_RANGES}&access_token=${TOKEN}&limit=1000`;

  const encodedFacebookUrl = encodeURI(facebookUrl);
  
  const options = {
    'method' : 'post'
  };
  
  // Fetches & parses the URL 
  const fetchRequest = UrlFetchApp.fetch(encodedFacebookUrl, options);
  const results = JSON.parse(fetchRequest.getContentText());


// Formats only spend in the cell

var data = [];


results.data.forEach(function(pieceOfData){
  data.push(pieceOfData.spend)
});

return data;

}

I've removed the access token, but that would usually be there. I'm able to open the website, by entering all the right variables, and then the data displays. Screenshot attached of how it looks. Now trying to figure out how to exclude only the number after the spend part and to show it in the cell where the formula is in. With the current code, the cell with the formula is empty.

Any help or suggestions is appreciated, I'm trying to save me and my coworkers about 30% of our time :D

screenshot

The data that I'm able to get from FB is the following:

{
   "data": [
      {
         "spend": "41389.73",
         "date_start": "2022-08-01",
         "date_stop": "2022-08-08"
      }
   ],
   "paging": {
      "cursors": {
         "before": "MAZDZD",
         "after": "MAZDZD"
      }
   }
}

The end goal is that 41389.73 shows up in the cell with the formula.

CodePudding user response：

If I understand correctly you're trying to get the value of spend which is stored under results and then return it, this is how to get it based on the example:

var spend = results.data[0].spend;
return spend;

This assumes that you get only one set of data values.

CodePudding user response：

If this data structure is always returned, you should instead programmatically obtain the values of the results constant and format them yourself to an array of your desired size. The reason why I suggest this is because the structure you have is not a simple 1D or 2D array, it is the following:

const: results
├─ array: data
│  ├─ object
│  │  ├─ spend
│  │  ├─ date_start
│  │  ├─ date_stop
├─ paging
│  ├─ cursors
│  │  ├─ before
│  │  ├─ after

To obtain the exact value of the spend property you will need to open the array, they use indexes and the first index is 0 just like @Bryan said.

I suggest you use this code after the const results = JSON.parse(fetchRequest.getContentText()); line:

var data = [];
  
results.data.forEach(function (pieceOfData){
  data.push(pieceOfData.spend);
});

return data;

Since data is an array you could receive multiple spending values, this will list them all in a different row.