Skip to content

Latest commit

 

History

History
27 lines (18 loc) · 1.23 KB

README.md

File metadata and controls

27 lines (18 loc) · 1.23 KB
Raw

GAS Tasks

General pieces of code for specific purposes. Much of the code here needs to be cleaned up.

Download a set of lists from a webpage

The Sheets function IMPORTHTML can be used to download both tables and lists from webpages. Unfortunately, Google Sheets does not provide a means to call spreadsheet functions as VBA does where you can use the WorksheetFunction_ to call Excel functions. However, with a little extra work, we can set the formula of a cell with a call to the function.

In this example, I wished to download 29 lists from a webpage.

Task:

  • There are 29 lists to download starting at list 4 up to 29 on the webpage.

The approach is as follows:

  • Construct the call to function IMPORTHTML in a loop passing in the list number.
  • Set the value of cell A1 to the value of the function call:
    • =IMPORTHTML("https://www.doctoralerts.com/list-pathogenic-viruses/", "list", ${i})
  • Collect the returned values into an array
  • When all lists have been collected, instert a new sheet.
  • Loop over the array to write its element values to consecutive rows in the new sheet.

Source code