CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
xNemox
xNemox

Reputation: 11

Automatically save data to Google sheets with Google AppScript

I have a Sheet called 'Automatic' where I have imported a list of data with IMPORTXML.

Now I would like to update the list of data every day at the same time to periodically build a database with different stock quotes.

How can I do this via Google AppScript?

This is the formula I'm using to get a hold of the values I need:

=IMPORTXML("https://www.avanza.se/aktier/om-aktien.html/5447/abb-ltd","//dl[@class='border XSText rightAlignText noMarginTop highlightOnHover thickBorderBottom noTopBorder']//dd")

Thank you for all your help, and if I left out any part you need to know about, just ask!

Picture of my sheet

Upvotes: 0

Views: 2551

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

You can add this to your script editor. Save and run it. Approve the popups. Then go to Edit in the script editor menu and select 'current project's triggers'. Set the trigger to run this function daily at the hour you want.

function refreshData() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getSheetByName("Sheet1")
  var lr= s.getLastRow()
  var data= s.getRange(1,3,lr,1).getValues()
  var s1=ss.getSheetByName("Sheet2")
  var lr1=s1.getLastRow()
  var dte=s1.getRange(lr1+1, 3, 1, 1).setValue(new Date())
  var lr1=s1.getLastRow()
  var setdte=s1.getRange(lr1+1, 3, data.length, 1).setValues(data)
  var clr=s.getRange("C1").clearContent()
  var   cell=s.getRange("C1").setFormula('=IMPORTXML("https://www.avanza.se/aktier/om-aktien.html/5447/abb-ltd\",\"//dl[@class=\'border XSText rightAlignText noMarginTop highlightOnHover thickBorderBottom noTopBorder\']//dd\")')
 }

I added making a copy to Sheet2 with date. It adds copy after last entry.

Upvotes: 3

Related Questions