How to Paste object data to google sheet?

user_4156645 0
Viewed 63

How can I paste the data (row and column) passed by the function makeRequest to google sheet.

I am retrieving the data from HTML storing that into an object and passing that object like this makeRequest(facebookAccountData).

I can use something like this below to paste data (row and column) to sheet but in my case user will define which fields he requires. So I want to make this dynamic which I am not able do so.

   const imp = data.data[i]["clicks"];
   const clicks = data.data[i]["impressions"];
   const row = [imp,clicks];

Code.gs

function makeRequest(facebookAccountData) {
   
  
  // get API Service Service
  .. API Logic goes here.

  //set up the API Endpoint

  const base = 'https://graph.facebook.com/v10.0/';
  
  const endpoint = 'act_' +facebookAccountData.adAccount+ '/insights?' + '&fields=' + facebookAccountData.facebookFields + '&date_preset=' + facebookAccountData.startDate + '&level=ad'
  

  const url = base + endpoint;
  console.log(url);

  const params = {

    headers: {

      Authorization: 'Bearer ' + access_token
    }
    
  }
  
  const response = UrlFetchApp.fetch(url,params);
  console.log(response);

  const data = JSON.parse(response);
  console.log(data);

// CONSOLE DATA LOG RESULT

// { data: 
//    [ { campaign_name: 'XXX',
//        clicks: '25',
//        impressions: '765',
//        date_start: '2021-06-10',
//        date_stop: '2021-06-10' } ],
//   paging: { cursors: { before: 'MAZDZD', after: 'MAZDZD' } } }



pasteDataToSheet (data);
  
}

function pasteDataToSheet (data) {
  for (var i=0; i<data.data[0].length; i++) {
   const row = [dat.data[i]];
   SpreadsheetApp.getActiveSheet().appendRow(row);
    console.log(row);
}
google-apps-script google-sheets
1 Answers

In your situation, how about the following modification? I thought that when setValues is used instead of appendRow, the process cost will be able to be reduced a little. Ref

From:

pasteDataToSheet (data);

To:

const sheet = SpreadsheetApp.getActiveSheet();
const headers = Object.keys(data.data[0]);
const values = [headers, ...data.data.map(o => headers.map(h => o[h]))];
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

Note:

  • If you don't want to add the header row, please modify const values = [headers, ...data.data.map(o => headers.map(h => o[h]))]; to const values = data.data.map(o => headers.map(h => o[h]));.

  • In this modification, it supposes that the value of data is as follows.

      {
    data: [{
      campaign_name: 'XXX',
      clicks: '25',
      impressions: '765',
      date_start: '2021-06-10',
      date_stop: '2021-06-10'
    }],
    paging: { cursors: { before: 'MAZDZD', after: 'MAZDZD' } }
  }

  • If you want to use the header of ["clicks", "impressions"], please modify const headers = Object.keys(data.data[0]); to const headers = ["clicks", "impressions"];.

References:

user_71086530
Related