I have a function in Google Apps Script that fetches to a serverless api I created which returns our company's Facebook ad spend for the previous day.

function fetchAdData() {
  const url = SERVERLESS_ENDPOINT
  let response = UrlFetchApp.fetch(url);
  const json = response.getContentText();
  const GBP = Intl.NumberFormat("en-GB", {
    style: "currency",
    currency: "GBP",
    minimumFractionDigits: 2
  });
  let data = JSON.parse(json)
  let result = data.map((ad) => {
    return [ad.date, ad.campaign, GBP.format(ad.spend)]
  })
return result
}

I'm using a macro and daily trigger to make the function run daily to give us a daily rundown of the previous day's campaigns and spend:

function DailyAdSpendFetch() {
  const ss = SpreadsheetApp.getActive();
  const spreadsheet = ss.getSheetByName('ADSPEND');
  let data = '=fetchAdData()'
  spreadsheet.appendRow([data])
};

The functions work as expected but are overwriting the previous day's values whenever it is called. So for example instead of having:

I'm getting:

I've tried setting the value instead of appending the row :

function DailyAdSpendFetch() {
  var ss = SpreadsheetApp.getActive();
  var spreadsheet = ss.getSheetByName('ADSPEND');
  let data = '=fetchAdData()'
  let lastRow = spreadsheet.getLastRow()
  let activeCell = spreadsheet.getRange(`A${lastRow}`);
  spreadsheet.setCurrentCell(activeCell);
  spreadsheet.getCurrentCell().offset(1, 0).activate();
  spreadsheet.getCurrentCell().setValue([data]);
};

But I get the same result.

This is my first time working in Google Apps Script so any help would be massively appreciated!

CodePudding user response：

This should do. All in one function. You get the last row of the sheet and add 1 as starting point to set the values. Also the Intl.NumberFormat changes your number to formatted text so you cannot use it in you're sheet to sum. Do the formatting in you're sheet.

function fetchAdData() {
  const url = SERVERLESS_ENDPOINT
  const response = UrlFetchApp.fetch(url);
  const result = JSON.parse(response.getContentText()).map((ad) => {
    return [ad.date, ad.campaign, ad.spend]
  })

  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('ADSPEND');

  sheet.getRange(sheet.getLastRow()   1, 1, result.length, result[0].length).setValues(result)
}