how to use google apps script to store price data in google sheets€¦ · how to use google apps...

2
How to Use Google Apps Script to Store Price Data in Google Sheets This guide is designed to accompany the article: Import and Store Live Price Data in Google Sheets. Step 1: Set up the Spreadsheet as described in the article: Step 2: Click: Tools – Script Editor… Step 3: Click on Untitled project in the top left and rename the script page. Delete any text in the Script Editor and then copy the following text. This is a function that will store the current price data in the cell below. Click Save and then click the click the run button Allow Authorization for the Script to run. You should now see the spreadsheet has copied the live price data to the row below. If necessary format the date so it matches the cell above. This script will copy down the data to the row below every time it is run, except when the closing price is the same. Step 4: The next step is to create a time schedule to run the script automatically. I have set up this script to run every weekday between 5pm and 6pm. Copy the following text into the Script Editor below the previous function. function copyPriceData(){ var sheet = SpreadsheetApp.getActiveSheet(); // Get more recent closing prices var closePriceRange = sheet.getRange("F4"); var prevClosePriceRange = sheet.getRange("F5"); var closePrice = closePriceRange.getValue(); var prevClosePrice = prevClosePriceRange.getValue(); // Check if price data has updated. If so create new row and copy price data. if (closePrice != prevClosePrice) {sheet.insertRowsAfter(4, 1); var rangeToCopy = sheet.getRange("B4:F4"); rangeToCopy.copyTo(sheet.getRange("B5:F5"), {contentsOnly:true}); } }

Upload: vuongdat

Post on 29-Aug-2018

244 views

Category:

Documents


3 download

TRANSCRIPT

Page 1: How to Use Google Apps Script to Store Price Data in Google Sheets€¦ · How to Use Google Apps Script to Store Price Data in Google Sheets This guide is designed to accompany the

How to Use Google Apps Script to Store Price Data in Google Sheets This guide is designed to accompany the article: Import and Store Live Price Data in Google Sheets.

Step 1: Set up the Spreadsheet as described in the article:

Step 2: Click: Tools – Script Editor…

Step 3: Click on Untitled project in the top left and rename the script page.

Delete any text in the Script Editor and then copy the following text. This is a function that will store

the current price data in the cell below.

Click Save and then click the click the run button

Allow Authorization for the Script to run.

You should now see the spreadsheet has copied the live price data to the row below. If necessary

format the date so it matches the cell above.

This script will copy down the data to the row below every time it is run, except when the closing

price is the same.

Step 4: The next step is to create a time schedule to run the script automatically. I have set up this script to

run every weekday between 5pm and 6pm.

Copy the following text into the Script Editor below the previous function.

function copyPriceData(){ var sheet = SpreadsheetApp.getActiveSheet(); // Get more recent closing prices var closePriceRange = sheet.getRange("F4"); var prevClosePriceRange = sheet.getRange("F5"); var closePrice = closePriceRange.getValue(); var prevClosePrice = prevClosePriceRange.getValue(); // Check if price data has updated. If so create new row and copy price data. if (closePrice != prevClosePrice) {sheet.insertRowsAfter(4, 1); var rangeToCopy = sheet.getRange("B4:F4"); rangeToCopy.copyTo(sheet.getRange("B5:F5"), {contentsOnly:true}); } }

Page 2: How to Use Google Apps Script to Store Price Data in Google Sheets€¦ · How to Use Google Apps Script to Store Price Data in Google Sheets This guide is designed to accompany the

Click save.

Step 5: Using the drop-down menu. Select the function triggerUpdatePrice. Then click run. Allow

Authorization for the Script to run.

Select the other triggerUpdatePrice functions and run each one in turn.

Click on the Triggers button You should now see a list of each trigger. You can change the time

of the script to suit when you want the script to run.

For lots more resources about trading and analysing the financial markets please go to

Tradinformed.com

function triggerUpdatePrice(){ ScriptApp.newTrigger('copyPriceData') .timeBased() .onWeekDay(ScriptApp.WeekDay.MONDAY) .atHour(17) .create(); } function triggerUpdatePrice1(){ ScriptApp.newTrigger('copyPriceData') .timeBased() .onWeekDay(ScriptApp.WeekDay.TUESDAY) .atHour(17) .create(); } function triggerUpdatePrice2(){ ScriptApp.newTrigger('copyPriceData') .timeBased() .onWeekDay(ScriptApp.WeekDay.WEDNESDAY) .atHour(17) .create(); } function triggerUpdatePrice3(){ ScriptApp.newTrigger('copyPriceData') .timeBased() .onWeekDay(ScriptApp.WeekDay.THURSDAY) .atHour(17) .create(); } function triggerUpdatePrice4(){ ScriptApp.newTrigger('copyPriceData') .timeBased() .onWeekDay(ScriptApp.WeekDay.FRIDAY) .atHour(17) .create(); }