This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script.
Handy little code pattern for clearing ranges across multiple tabs in Google Sheets. Snippets are provided for both SpreadsheetApp and the advanced Sheets service.
Another useful discovery from Kanshi Tanaike this time highlighting the ability to use markdown in Google Apps Script code comments. For those unfamiliar, JSDoc is the syntax used to automatically generate inline documentation in Google Sheets custom functions,Libraries as well as function references in your script project. As highlighted in the post as well as being able to add @constructor tags, developers can use markdown syntax to provide additional formatting to documentation comments. See the source post for details of supported markdown syntax.
This is a sample script for retrieving the values of dropdown list of the smart chips on Google Document using Google Apps Script.
At August 23, 2021, 3 Classes for retrieving the smart chips have been added to Google Apps Script. But, in the current stage, unfortunately, all values of the smart chips cannot be retrieved by the Classes. For example, the dropdown list of the smart chips cannot be retrieved
Incredibly useful report and workaround from Kanshi Tanaike for Google Workspace Devs needing to get some ‘smart chips’ values from Google Docs. Hopefully classes/methods will be added to Apps Script and the Google Docs API (here is a related feature request you can star in the issue tracker), particularly as the current solution is to convert the Google Doc to .docx and then back to Google Doc.
Note: Process time has been graphed on a log scale
In order to retrieve the values from XML data, when XML data is parsed using Google Apps Script, there are several methods for parsing the data. … For example, Class XmlService cannot only parse and read XML data but also update XML data and create new XML data. In the current stage, the process cost of Class XmlService is much higher than those of “cheerio” and the simple script using regex.
In Pulse we’ve previously highlighted the cheerio JavaScript library for parsing/extracting content from XML data. The cheerio library can easily be added to Google Apps Script projects as a library (see the GitHub repo for installation). Google Apps Script also has the XML Service, which can also be used to work with XML data. In this latest benchmark report from Kanshi Tanaike they look at the process cost for reading XML data using XML Service, cheerio and using RegEx. The results highlight a higher process cost for using XML Service particularly if you are handling reading 1,000 or more XML elements. The source post contains further details of the test and a summary of the results.
When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.
A great feature of Google Apps Script is the ability to create and serve custom HTML, often used to interface data you have in Google Workspace such as Google Sheets. Google highlight a coupe of different ways you can mix Apps Script code and HTML. Some of these ways are better in terms of process time and this report from Kanshi Tanaike highlights the cost of calling Apps Script functions as scriptlets in HTML templates. The good news is you can avoid delays in your web app rendering by making asynchronous calls with google.script.run, which you can read more about in Google’s best practices documentation.
Update: I’ve replicated this benchmark (smaller dataset) with google.script.run and it was only marginally slower (0.3s) than the ‘create HTML table with Google Apps Script’:
Recently, I answered this thread. In that case, in order to convert the sample python script to Google Apps Script, the script for retrieving the signature might be a bit complicated. So, here, I would like to introduce this.
Gate.io is a cryptocurrency exchange which supports trading of a wide range of blockchain based currencies. The platform provides an API allowing users to interact and use features of Gate.io. Whilst it is unlikely that the majority of Google Workspace developers will be interested in interacting with the Gate API this post from Kanshi Tanaike might still be interesting to see how Python code has been refactored for Google Apps Script. It might also be useful should you encounter other APIs that require similar signature requests.
This is a sample script for retrieving and parsing the XML data from Google Workspace Update Blog and putting it to Google Spreadsheet using Google Apps Script.
While this post from Kanshi Tanaike focuses on parsing the XML feed from the Google Workspace Update Blog the code can easily be modified to pull other XML feeds.
Even if you are not interested in parsing XML to Google Sheets the code pattern in this solution is worth looking at as it uses a destructuring assignment and reduce() to construct the .setValues() array for writing data to Google Sheets.
In Kanshi Tanaike’s script they clear the contents each time the script runs. If you would like insert new posts keeping a record of previous blog updates here is a forked version which will insert new data.
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 :)
Fortunately, Kanshi Tanaike has been exploring the impact the increased volume of data in Google Sheets has when using Google Apps Script and both SpreadsheetApp and Sheets API. The linked report contains a number of useful findings and strategies for handling large Google Sheets with Apps Script.
This is a sample script for retrieving the summary of Google Document using Google Apps Script. Recently, a blog of Auto-generated Summaries in Google Docs has been posted. I thought that this is very interesting function. I thought that when this function is released, checking each summary of a lot of Google Document will be much useful for simply confirming the document content. And also, I thought that when all summaries can be retrieved using a script, it will be also useful. In this post, I would like to introduce to retrieve the summary of Google Document using Google Apps Script.
There are a couple of pieces of interesting information highlighted by this post by Kanshi Tanaike that caught my eye . First, useful to have a reminder that Google announced automatically generated summaries in Docs in February 2022. This might be a premium feature for the paid Workspace accounts as like Kanshi I’m not seeing this yet in my own free Google Workspace domain. The bit that really caught my eye is Google Docs summaries are using the existing Google Drive description property, which means it is not currently available via DocumentApp or if using the Google Docs API as an Advanced Service Docs. I’m sure this will catch some people out and hopefully this post will point people in the right direction.
