covid-19.js

covid-19.js

let ss = SpreadsheetApp.getActiveSheet();
let start_row = 22;
let forecast_base = 14; // Сколько последних дней использывать для интерполяции
let forecast_rows = 14; // На сколько дней вперед делать прогноз

function updateStat() {
  
  let res = UrlFetchApp.fetch("https://yastat.net/s3/milab/2020/covid19-stat/data/default_data.json?v=1604146089914", {
    "headers": {
      "accept": "*/*",
      "accept-language": "ru,en-US;q=0.9,en;q=0.8",
      "sec-fetch-dest": "empty",
      "sec-fetch-mode": "cors",
      "sec-fetch-site": "cross-site"
    },
    "referrer": "https://yandex.ru/covid19/stat?utm_source=main_notif&geoId=213",
    "referrerPolicy": "no-referrer-when-downgrade",
    "body": null,
    "method": "GET",
    "mode": "cors",
    "credentials": "omit"
  });
 
  let {russia_stat_struct} = JSON.parse(res);
  let {data, dates} = russia_stat_struct;
  
  // Заполняем регионы
  let reg_ids = Object.keys(data);
  let regs = reg_ids.map((id)=>data[id].info.short_name+` #${id}`);
  let population = reg_ids.map((id)=>data[id].info.population);
  let date = reg_ids.map((id)=>data[id].info.date);
  let cases = reg_ids.map((id)=>data[id].info.cases);
  let cases_delta = reg_ids.map((id)=>data[id].info.cases_delta);
  let deaths = reg_ids.map((id)=>data[id].info.deaths);
  let deaths_delta = reg_ids.map((id)=>data[id].info.deaths_delta);
  let cured = reg_ids.map((id)=>data[id].info.cured);
  let cured_delta = reg_ids.map((id)=>data[id].info.cured_delta);

  ss.getRange(2, 2, 9, regs.length).setValues([
    regs, population, date,
    cases, cases_delta,
    deaths, deaths_delta,
    cured, cured_delta])

  let end_row = start_row+dates.length;
  let dates_rng = ss.getRange(start_row, 1, dates.length, 1);
  let forecast_rng = ss.getRange(start_row, 1, dates.length+forecast_rows, 1);
  //Подкрашиваем прогноз
  dates_rng.setValues(dates.map((d)=>[d])).setBackground(null).setBorder(false,false,false, false,false,false);
  dates_rng.autoFill(forecast_rng, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  ss.getRange(end_row, 1, forecast_rows, 1).setBackground("#EEE").setBorder(true,false,false, false,false,false);
}

function updateRegion(id) {
  let res = UrlFetchApp.fetch(`https://yastat.net/s3/milab/2020/covid19-stat/data/data-by-region/${id}.json`, {
    "headers": {
      "accept": "*/*",
      "accept-language": "ru,en-US;q=0.9,en;q=0.8",
      "sec-fetch-dest": "empty",
      "sec-fetch-mode": "cors",
      "sec-fetch-site": "cross-site"
    },
    "referrer": "https://yandex.ru/covid19/stat?utm_source=main_notif&geoId=213",
    "referrerPolicy": "no-referrer-when-downgrade",
    "body": null,
    "method": "GET",
    "mode": "cors",
    "credentials": "omit"
  });
  
  let {cases,deaths,cured,info} = JSON.parse(res);
  let sick = info.cases-info.cured-info.deaths;
  let sick_delta = info.cases_delta-info.cured_delta-info.deaths_delta;
  ss.getRange(start_row-4, 2,1, 1).setValues([[info.short_name]]);
  ss.getRange(start_row-1, 1,1, 1).setValues([[info.date]]);
  ss.getRange(start_row-3, 2,1, 2).setValues([["Популяция: ", info.population]]);
  ss.getRange(start_row-2, 2,1, 12).setValues([[
    "Заразившихся" ,"%", "прирост",
    "Болеюших"     ,"%", "прирост",
    "Выздоровевших","%", "прирост",
    "Умерших"      ,"%", "прирост"
  ]]);
  ss.getRange(start_row-1, 2,1, 12).setValues([[
    info.cases , info.cases /info.population, info.cases_delta,
    sick       , sick       /info.population, sick_delta,
    info.cured , info.cured /info.population, info.cured_delta,
    info.deaths, info.deaths/info.population, info.deaths_delta,
  ]]);
  
  ss.getRange(start_row, 2, cases.length,12).setValues(cases.map((c,i)=>(
    sick  = cases[i][0]-cured[i][0]-deaths[i][0],
    sickd = cases[i][1]-cured[i][1]-deaths[i][1], 
    [
      cases [i][0], cases [i][0]/info.population, cases [i][1],
      sick        , sick        /info.population, sickd       ,
      cured [i][0], cured [i][0]/info.population, cured [i][1],
      deaths[i][0], deaths[i][0]/info.population, deaths[i][1]
    ])));
    
  // ТРЕНД
  let start = start_row+cases.length;
  let formulas = [...'BCDEFGHIJKLM'].map((c)=>
    `=TREND(${c}${start-forecast_base}:${c}${start-1};$A${start-forecast_base}:$A${start-1};$A${start}:$A${start+forecast_rows-1})`
  );
  ss.getRange(start,2,1,formulas.length).setFormulas([formulas])
  
  return info
}

function main() {
  updateStat();
  var dest = DriveApp.getFolderById("1Xa8pl2JBbwGRGDDFyMdfRgzwxadxPv23"); 
  let info;
  for(let id of [1,2,213,225,11079]) {
    Logger.log(id);
    info = updateRegion(id);
    SpreadsheetApp.flush();
    DriveApp.getFileById(SpreadsheetApp.getActive().getId()).makeCopy(`${info.date}.${info.short_name}.COVID-19 прогноз`, dest);
  }
}