如何正确使用谷歌可视化显示单元格'

How to correct cell's value displaying using Google visualization

本文关键字：单元格 显示 可视化 何正确 谷歌      更新时间：2023-09-26

我链接一个谷歌电子表格显示一些值在HTML中指向行索引和列索引的电子表格。括号内的值:

Abolenkin, Andrei (0,2)

它工作，但在编辑，排序，在相应的谷歌电子表格中添加新行后，单元格值与错误或空单元格交换:

Abolenkin, Andrei (0,0)

问题是如何正确地指向和修复链接单元格的值?

请查看电子表格和下面的代码

// https://google,developers.appspot.com/chart/interactive/docs/spreadsheets#gid
      google.load('visualization', '1', {packages: ['corechart', 'line']});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/1tTfV1DwfbipOvCMGMhekNIDRVDROwhrvofjA5YE2JHY/edit#gid=0range=A";
        var query = new google.visualization.Query(spreadsheetUrl);
        query.send(handleQueryResponse);
      }
      function handleQueryResponse(response) {
        var dataTable = response.getDataTable();
        // https://developers.google.com/chart/interactive/docs/reference?hl=en#methods
        // getValue(rowIndex, columnIndex)
        
        document.getElementById("Abolenkin").innerHTML = dataTable.getFormattedValue(0, 0);
        document.getElementById("Akhmadullina").innerHTML = dataTable.getFormattedValue(1, 0);
       var date = (new Date().getFullYear()).toString();
       document.getElementById("year").innerHTML = date;
      }
/* Indexmod CSS */
/* Лого */
.header {
padding:0px;
margin:30;
}
/* Страница */
.content {
	padding: 20px;
	margin: auto;
	font: 20px Georgia, serif;
	line-height: 27px;
}
h1 {
font-size:70px;
font-family: "Helvetica Neue", Arial, sans-serif;
font-weight:100;
-webkit-font-smoothing:antialiased;
}
li {
	list-style: none;
	list-style-type:none
}
a:link    {color:black; text-decoration:none}
a:visited {color:black; text-decoration:none}
a:hover   {color:black; text-decoration:underline}
a:active  {color:black; text-decoration:none}
/* Подвал */
.footer {
	margin: auto;
}
#year {
    vertical-align: super;
    font-family: Georgia;
    font-size: 12px;
    color: black;
}
.what {
    vertical-align: super;
    font-family: Georgia;
    font-size: 12px;
    color: black;
    
}
.name {
    font-weight: bold;
    
}
<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8" />
	<!--[if lt IE 9]><script src="https://cdnjs.cloudflare.com/ajax/libs/html5shiv/3.7.3/html5shiv.min.js"></script><![endif]-->
	
	<script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script src="code.js"></script>
</head>
<body>
<link href="style.css" rel="stylesheet"> 
<main class="content">
	    
	        <p id="Abolenkin-Andrei"> <span class=name>Abolenkin, Andrei</span> (<span id="Abolenkin"></span><a class=what href="http://www.indexmod.info/indexmod" title="Based on the Indexmod algorithm. Click for more info.">?</a>), Andrei Abolenkin; A.K.A Nekto Abolenkin; Rus. Андрей Аболенкин; Некто Аболенкин; born 1972, Moscow, is a Russian style and fashion expert, consultant and promoter. <a href="http://sites.google.com/site/indexmodencyclopedia/abolenkin-andrei"> Login to edit entire article indexmod.info/abolenkin-andrei</a></p>
    
    <p id="Akhmadullina-Alena"> <span class=name>Akhmadullina, Alena</span> (<span id="Akhmadullina"></span><a class=what href="http://www.indexmod.info/indexmod" title="Based on the Indexmod algorithm. Click for more info.">?</a>), Rus. Алёна Ахмадуллина, born 1978, Sosnovy Bor, Leningrad Oblast, is a Russian fashion designer, founder of the brand Alena Akhmadullina, participant of various Russian and international competitions in the field of fashion, мember of “Snob” from April 2009.</p>
</main>
</body>
</html>

如果电子表格已排序，则不能保证所需的值分别位于第0行和第1行

dataTable.getFormattedValue(0, 0) //<-- (row, column)

为了确保找到正确的值，可以在DataTable 上使用getFilteredRows方法。

参见以下工作代码片段中的findValue函数，
使用表格图表来帮助可视化问题

google.load('visualization', '1', {packages: ['corechart', 'table']});
google.setOnLoadCallback(drawChart);
function drawChart() {
  var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/1tTfV1DwfbipOvCMGMhekNIDRVDROwhrvofjA5YE2JHY/edit#gid=0range=A";
  var query = new google.visualization.Query(spreadsheetUrl);
  query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
  var dataTable = response.getDataTable();
  //var container = document.getElementById("chart_div");
  //var chart = new google.visualization.Table(container);
  //chart.draw(dataTable);
  document.getElementById("Abolenkin").innerHTML = findValue("Abolenkin");
  document.getElementById("Akhmadullina").innerHTML = findValue("Akhmadullina");
  function findValue(surname) {
    var value = '';
    var rowsFound = dataTable.getFilteredRows([{
      column: 7,
      value: surname
    }]);
    if (rowsFound.length > 0) {
      value = dataTable.getFormattedValue(rowsFound[0], 0);
    }
    return value;
  }
}
div {
  padding: 8px;
}
<script src="https://www.google.com/jsapi"></script>
<div>
  Abolenkin: <span id="Abolenkin"></span>
</div>
<div>
  Akhmadullina: <span id="Akhmadullina"></span>
</div>
<div id="chart_div"></div>

相关文章：