Advertisement
RemcoE33

Chart - JS

Sep 17th, 2021
68
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function onOpen(e){
  2.   SpreadsheetApp.getUi().createMenu('Utill')
  3.     .addItem('Show Chart', 'entrypoint')
  4.     .addToUi();
  5. }
  6.  
  7. function entrypoint() {
  8.   const ss = SpreadsheetApp.getActiveSpreadsheet();
  9.   const tempSheet = ss.getSheetByName('TempData');
  10.   const cell = ss.getActiveSheet().getActiveCell();
  11.   const sheet = cell.getSheet();
  12.   const ticker = sheet.getRange(cell.getRow(),1).getValue();
  13.  
  14.   const formulaRaw = /SPARKLINE\((.*?);{/g.exec(cell.getFormula())[1]
  15.   const a1 = /GOOGLEFINANCE\((.*?);/g.exec(formulaRaw)[1];
  16.   const formula = `=${formulaRaw.replace(a1,`"${ticker}"`)}`;
  17.   console.log(formula);
  18.   tempSheet.getRange(1,1).setValue(ticker);
  19.   tempSheet.getRange(2,1).setFormula(formula);
  20.   SpreadsheetApp.flush();
  21.   Utilities.sleep(1000);
  22.   createChart();
  23. }
  24.  
  25. function getChartData(){
  26.   const ss = SpreadsheetApp.getActiveSpreadsheet();
  27.   const tempSheet = ss.getSheetByName('TempData');
  28.   const data = tempSheet.getRange(3,2,tempSheet.getLastRow()-2).getValues().flat();
  29.   const labels = tempSheet.getRange(3,1,tempSheet.getLastRow()-2).getValues().flat()
  30.     .map(label => { return new Date(label).toLocaleDateString()})
  31.   const ticker = tempSheet.getRange(1,1).getValue();
  32.  
  33.   return {ticker, data, labels};
  34. }
  35.  
  36.  
  37.  
  38. function createChart(){
  39.   const html = HtmlService.createHtmlOutputFromFile("chart");
  40.   html.setHeight(500).setWidth(800);
  41.   SpreadsheetApp.getUi().showDialog(html);
  42. }
  43.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement