This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from GOOGLEFINANCE function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.
Another interesting report from Kanshi Tanaike where they test the ability to get values from the Google Sheets GOOGLEFINANCE function with Google Apps Script. For context, as highlighted in the report, Google announced in 2016 that historical data from the GOOGLEFINANCE function would no longer be accessible using either Google Apps Script or the Google Sheets API.
The 2016 announcement followed news in 2014 that the Finance Service, which allowed direct access to current and historical stock data, was deprecated (if you are interested in seeing what you are missing out on here is the Internet Archive snapshot of Finance Service from 2013).
So clearly there is a trend here in Google not wanting you to programmatically access stock data with their services and I anticipate the GOOGLEFINANCE function will be updated to prevent access from Google Apps Script, but in the meantime you can have some fun :)
Master the QUERY function, the most powerful function in Google Sheets, to become a more effective data analyst
Friend of Pulse and Google Sheets guru, Ben Collins, has a new course âThe QUERY Function in Google Sheetsâ. The QUERY function it lets you perform various data manipulations making it easy to reshape, aggregate and explore your data in Google Sheets. The course is designed to be suitable for everyone from beginner to advanced who are interested in ways to work more effectively with your data.
If you are not familiar with the QUERY function Ben provides one example of what is possible in Sheets Tip 204: How To Use Dates In The QUERY Function (check the linked post in this Sheet Tip for an example worksheet).
Bonus:Sheets Tip 204 includes a 50% discount on the course valid until Friday 20 May 2022 at midnight EDT.
Finally, if you are interested in using the QUERY language in Google Apps Script it is possible! Below is some code used in this copy of Ben’s example workbook based on:
// based on https://gist.github.com/tanaikech/053d3ebbe76fa7c0b5e80ea9d6396011#sample-script
function myFunction() {
const doc = SpreadsheetApp.getActive()
const spreadsheetId = doc.getId(); // or set another Spreadsheet ID.
const sheetId = doc.getSheetByName('Data').getSheetId(); // or set another Sheet ID from Spreadsheet ID.
const query = "select C, B where B > date '2000-01-01' and B <= date '2002-12-31'"; // your QUERY
const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodeURI(query)}&access_token=${ScriptApp.getOAuthToken()}`;
const res = UrlFetchApp.fetch(url,);
console.log(res.getContentText());
const array = Utilities.parseCsv(res.getContentText());
console.log(array);
// SpreadsheetApp.getActiveSpreadsheet(); // This comment line is put for automatically detecting the scopes if directly adding a spreadsheet ID.
}
One the powerful features of Google Apps Script is with a couple of lines of code you can quickly start automating and organising your inbox. For people just getting started with Apps Script Google provide a introductory codelab ‘Accessing Google Sheets, Maps, and Gmail in 4 lines of code!’Â and for those wanting to do a bit more the Google Workspace Developer documentation includes a ‘create a mail merge‘ solution.
Mail merge solutions are bit or a reoccurring theme in the world of Google Apps Script. In 2011 the official G Suite Developers Blog featured 4 ways to do Mail Merge using Google Apps Script with community contributions from James Ferreira, Steve Webster and Romain Vialard. The post references Romain’s ‘Yet another Mail Merge’ script, which he went on to develop as a very successful YAMM add-on before refocusing on the Mergo Mail Merge. Even further back when Google Apps Script was officially launched in 2009 this included a introductory video with a mail merge example.
A well as Google official channels the ability to automate your Gmail inbox has regularly caught the attention of the wider tech press. In 2013, Computerworld highlighted Jonathan Kim’s ‘Gmail No Response’ script which goes through your inbox and finds recent emails where you were the last respondent. Jonathan’s blog post is no longer available but the Gmail No Response’ script is on GitHub where it has been forked 100 times.
runs through the emails in your inbox and checks your outgoing messages for a question mark. Once it finds these emails it checks to see if they are in a date range and then sees if you have had a response. If you have not had a reply to your email containing a â?â then it adds the label âNo Responseâ. You can then quickly see all of the threads for which you are awaiting a reply.
If you are interested in more Gmail script solutions then I highly recommend you have a look at content shared by Amit Agarwal. This doesn’t just include Apps Script solutions but also a number of Gmail tips and tricks. As many of these feature the way you can search your Gmail inbox they can also be used with GmailApp.search() or if using the Gmail Advanced Service Gmail.Users.Messages.list. A nice example of where a Gmail user tip can be used in Apps Script is Amit’s post which includes how to Search Emails by Specific Time in Gmail.
Working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.
For displaying dates/time values for users in different time zones you can use Utilities.formatDate(date, timeZone, format), the challenge however is working out the user’s timeZone. For Google Workspace Add-ons developers can configure the manifest option to include user locale information in event objects included as part of action callback functions [See Accessing user locale and timezone].
function getUserTimeZone() {
var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
Logger.log(userTimeZone)
}
The downside of this approach is it potentially adds an additional authorisation scope the user will need to approve before the script can run. As noted by Google best practice is always to limit the scopes in your Apps Script projects to the ones you need.
If you’ve got other tips for handling user time zones feel free to pop them in the comments.
const event = CalendarApp.getEventById('some_event_id');
const guestList = event.getGuestList(true); // get the guest list including owner
// Iterate across EventGuest[]
for (let i = 0; i < guestList.length; i++){
let guest = guestList[i].getEmail();
let status = guestList[i].getGuestStatus().toString();
Logger.log(guest + ' ' + status);
}
However, in practice as the organiser status defaults to ‘Yes’ and can change to ‘No’ or ‘Maybe’ the OWNER status is never returned:
How to solve? Well one solution to find the owner is to get the event guest list with and without the owner then filter out the list ignoring any accounts that appear twice:
const event = CalendarApp.getEventById('some_event_id);
const guestList = event.getGuestList(true); // get the guest list including owner
const guestListWithoutOwner = event.getGuestList(); // get the guest list without the owner
// filter the guest list to ingore duplicate emails leaving only the owner
// based on https://stackoverflow.com/a/42995029/1027723
const owner = guestList.filter(o1 => !guestListWithoutOwner.some(o2 => o1.getEmail() === o2.getEmail()));
Logger.log('The owner is: ' + owner[0].getEmail());
Alternatively if the Calendar Advanced Service is enabled in your Apps Script project the owner email can be returned using:
// With Calendar Advanced Service enabled
// Note: eventId is different to the one returned by CalendarApp as it doesn't include @google.com
// See https://stackoverflow.com/a/55545091/1027723 for how to handle
const event = Calendar.Events.get(CalendarApp.getDefaultCalendar().getId(), 'some_event_id');
Logger.log('The owner is: ' + event.organizer.email);
Clearly the second method is more direct, but which is quicker? I’ll let you tell me :)
