How to build a URL with a variable for importing daily data in Google Sheets (using Apps Script)?-CodePudding

I want to automatically scrape data on a daily basis with the IMPORTDATA-function in Google Sheets. I am using an URL-based API. My problem is that in the structure of the URL a certain date has to be inserted. I do not know how to turn the date into a variable so that the URL automatically changes every day.

The URL for yesterday's data is: https://www.benzinpreis.de/statistik.phtml?o=4&so=b.order_total&cnt=50&mystatart=LKR&mystat=DAY13.11.2022 00:00:00

My code now looks like this:

function TagesdurchschnittDeutschland() {
  let yesterday = new Date()
  yesterday.setDate(yesterday.getDate()-1);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('TagesdurchschnittDeutschland'))
  spreadsheet.getRange('A1').activate()
.setFormula('=IMPORTHTML("https://www.benzinpreis.de/statistik.phtml?o=4&so=b.order_total&cnt=50&mystatart=LKR&mystat=DAY'   yesterday.getDate()   '.'   yesterday.getMonth()   '.'   yesterday.getFullYear()   ' 00:00:00"; "table"; 5)');

}

CodePudding user response：

This function will return the URL for yesterday. It is dependent on the timezone defined for your Apps Script, which you can change in the global settings of your script.

function makeYesterdayUrl() {
  let yesterday = new Date()
  yesterday.setDate(yesterday.getDate()-1);
  return 'https://www.benzinpreis.de/statistik.phtml?o=4&so=b.order_total&cnt=50&mystatart=LKR&mystat=DAY'
      yesterday.getDate().toString().padStart(2, '0') 
      '.' 
      (yesterday.getMonth() 1).toString().padStart(2, '0') 
      '.' 
      yesterday.getFullYear()
      ' 00:00:00'
}

Note to myself, never forget that getMonth starts at 0 (for January), not 1... (ref)