Scraping of data rendered at the server-side about tableau-scraping HOT 11 CLOSED

from tableauscraper import TableauScraper as TS
import pandas as pd
import tableauscraper
import json
import time
import requests

#filter function from issue 26
def force_setFilter(wb, ws_name, columnName, values):
    "setFilter but ignore the listed filter options. also gets around wrong ordinal value which makes index value incorrect"

    scraper = wb._scraper
    tableauscraper.api.delayExecution(scraper)
    ws = next(ws for ws in wb.worksheets if ws.name == ws_name)

    filter = next(
        {
            "globalFieldName": t["globalFieldName"],
        }
        for t in ws.getFilters()
        if t["column"] == columnName
    )

    payload = (
        ("dashboard", scraper.dashboard),
        ("globalFieldName", (None, filter["globalFieldName"])),
        ("qualifiedFieldCaption", (None, columnName)),
        ("membershipTarget", (None, "filter")),
        ("exclude", (None, "false")),
        ("filterValues", (None, json.dumps(values))),
        ("filterUpdateType", (None, "filter-replace"))
    )
    try:
        r = scraper.session.post(
            f'{scraper.host}{scraper.tableauData["vizql_root"]}/sessions/{scraper.tableauData["sessionid"]}/commands/tabdoc/dashboard-categorical-filter',
            files=payload,
            verify=scraper.verify
        )
        scraper.lastActionTime = time.time()

        if r.status_code >= 400:
            raise requests.exceptions.RequestException(r.content)
        resp = r.json()
        errors = [
            res['commandReturn']['commandValidationPresModel']['errorMessage']
            for res in resp['vqlCmdResponse']['cmdResultList']
            if not res['commandReturn'].get('commandValidationPresModel', {}).get('valid', True)
        ]
        if errors:
            wb._scraper.logger.error(str(", ".join(errors)))
            raise tableauscraper.api.APIResponseException(", ".join(errors))

        wb.updateFullData(resp)
        return tableauscraper.dashboard.getWorksheetsCmdResponse(scraper, resp)
    except ValueError as e:
        scraper.logger.error(str(e))
        return tableauscraper.TableauWorkbook(
            scraper=scraper, originalData={}, originalInfo={}, data=[]
        )
    except tableauscraper.api.APIResponseException as e:
        wb._scraper.logger.error(str(e))
        return tableauscraper.TableauWorkbook(
            scraper=scraper, originalData={}, originalInfo={}, data=[]
        )

# since the most data entries are concentrated in a single sector, I start by scraping all of the other sectors
pdList = []
url = "https://public.tableau.com/views/SknadertilForskningsrdet-oppsummering/Enkeltsknader"

ts = TS()
ts.loads(url)
workbook = ts.getWorkbook()
ws = ts.getWorksheet("Prosjektoversikt")
wb = ws.setFilter('Sektor', 'Annet')  # I choose this sector as an init
wb = ws.setFilter('MDY(Søknadsfrist dato)', 20210210) # then I select the relevant date

#set the filter to all the sectors of interest
filtered = force_setFilter(workbook, "Prosjektoversikt", 'Sektor', ['Annet', 'Helseforetak', 'Instituttsektor', 'Næringsliv', 'Ukjent'])
df = filtered.getWorksheet("Prosjektoversikt").data
pdList.append(df)


#next I focus only on the remaining items in the UoH-sektor
#I create a list of lists with names of applicants, the goal is to keep the chunks under 800 items
filters = [['AHO', 'BI', 'Centre for the Science of Learning and Technology', 'CK', 'DMMH', 'FHS', 'Forskningsavdelingen PHS', 'HIB', 'HIHM', 'HIL', 'HIM', 'HINN', 'HIØ', 'HVL', 'HVO', 'MF', 'NHH', 'NIH'],
           ['Njord - Senter for studier av jordens fysikk', 'NMBU', 'NORD', 'NTNU', 'OSLOMET', 'PHS', 'SAMISKHS', 'Senter for bioinformatikk'],
           ['UIA', 'UIB', 'UIN', 'UIS', 'UIT', 'UNIS', 'USN', 'VID']
          ]

for filter in filters:
    url = "https://public.tableau.com/views/SknadertilForskningsrdet-oppsummering/Enkeltsknader"

    print(filter)
    
    ts = TS()
    ts.loads(url)
    workbook = ts.getWorkbook()
    ws = ts.getWorksheet("Prosjektoversikt")
    wb = ws.setFilter('Prosjektansvarlig', 'UIO') #I use UIO as an init here
    wb = ws.setFilter('MDY(Søknadsfrist dato)', 20210210)

    filtered = force_setFilter(workbook, "Prosjektoversikt", 'Prosjektansvarlig', filter)
    
    df = pd.DataFrame(filtered.getWorksheet("Prosjektoversikt").data)
    pdList.append(df)
    time.sleep(5)  #Not sure if this is necessary but I have had more reliable results with the delay

#Lastly, even the init value must be included
ts = TS()
ts.loads(url)
workbook = ts.getWorkbook()
ws = ts.getWorksheet("Prosjektoversikt")
wb = ws.setFilter('Prosjektansvarlig', 'UIO')
wb = ws.setFilter('MDY(Søknadsfrist dato)', 20210210)

df = wb.getWorksheet("Prosjektoversikt").data
pdList.append(df)

result = pd.concat(pdList, ignore_index=True)

from tableauscraper import TableauScraper as TS
import pandas as pd

# since the most data entries are concentrated in a single sector, I start by scraping all of the other sectors
pdList = []
url = "https://public.tableau.com/views/SknadertilForskningsrdet-oppsummering/Enkeltsknader"

ts = TS()
ts.loads(url)
rootWs = ts.getWorksheet("Prosjektoversikt")
wb = rootWs.setFilter('Sektor', 'Annet')  # I choose this sector as an init
# then I select the relevant date
wb = rootWs.setFilter('MDY(Søknadsfrist dato)', 20210210)

# get the worksheet again since the filter list has changed
ws = wb.getWorksheet("Prosjektoversikt")
print(ws.getFilters())

# set the filter to all the sectors of interest
filtered = ws.setFilter('Sektor', [
    'Annet', 'Helseforetak', 'Instituttsektor', 'Næringsliv', 'Ukjent'])
df = filtered.getWorksheet("Prosjektoversikt").data
pdList.append(df)

# next I focus only on the remaining items in the UoH-sektor
# I create a list of lists with names of applicants, the goal is to keep the chunks under 800 items
filters = [['AHO', 'BI', 'Centre for the Science of Learning and Technology', 'CK', 'DMMH', 'FHS', 'Forskningsavdelingen PHS', 'HIB', 'HIHM', 'HIL', 'HIM', 'HINN', 'HIØ', 'HVL', 'HVO', 'MF', 'NHH', 'NIH'],
           ['Njord - Senter for studier av jordens fysikk', 'NMBU', 'NORD',
               'NTNU', 'OSLOMET', 'PHS', 'SAMISKHS', 'Senter for bioinformatikk'],
           ['UIA', 'UIB', 'UIN', 'UIS', 'UIT', 'UNIS', 'USN', 'VID']
           ]

for filter in filters:
    url = "https://public.tableau.com/views/SknadertilForskningsrdet-oppsummering/Enkeltsknader"

    print(filter)
    ts = TS(delayMs=1000)  # 1 second delay between calls
    ts.loads(url)
    ws = ts.getWorksheet("Prosjektoversikt")
    # I use UIO as an init here
    wb = ws.setFilter('Prosjektansvarlig', 'UIO')
    wb = ws.setFilter('MDY(Søknadsfrist dato)', 20210210)

    ws = wb.getWorksheet("Prosjektoversikt")

    filtered = ws.setFilter('Prosjektansvarlig', filter)

    df = pd.DataFrame(filtered.getWorksheet("Prosjektoversikt").data)
    pdList.append(df)

# Lastly, even the init value must be included
ts = TS()
ts.loads(url)
ws = ts.getWorksheet("Prosjektoversikt")
wb = ws.setFilter('Prosjektansvarlig', 'UIO')
wb = ws.setFilter('MDY(Søknadsfrist dato)', 20210210)

df = wb.getWorksheet("Prosjektoversikt").data
pdList.append(df)

result = pd.concat(pdList, ignore_index=True)

print(result)

wb = ws.setFilter('MDY(Søknadsfrist dato)', [20200525, 20210210], filterDelta=True)

from tableauscraper import TableauScraper as TS
import pandas as pd

url = "https://public.tableau.com/views/SknadertilForskningsrdet-oppsummering/Enkeltsknader"

ts = TS()
ts.loads(url)
workbook = ts.getWorkbook()
rootWs = ts.getWorksheet("Prosjektoversikt")

dates = [
    t
    for t in rootWs.getFilters()
    if t["column"] == "MDY(Søknadsfrist dato)"
][0]["values"]
print(dates)

for date in dates:
    print(f"get date {date}")
    wb = rootWs.setFilter('MDY(Søknadsfrist dato)', date)
    ws = wb.getWorksheet("Antall prosjekter og søknader")

    tooltipHtml = ws.renderTooltip(x=143, y=3)
    df = pd.read_html(tooltipHtml)[0]
    print(df)

    tooltipHtml = ws.renderTooltip(x=342, y=11)
    df = pd.read_html(tooltipHtml)[0]
    print(df)