Ad

How Do I Pull Text From Multiple Cells In A Google Spreadsheet To Website Each In A Different

Tag?

- 1 answer

I am working on a webpage for a client. One of the requirements is that they are able to easily edit the pictures along with the items' descriptions on their gallery pages. I have a google sheet with item descriptions. I would like to pull these descriptions out of their cells and put them into <p> tags under the items' pictures. So far I have been able to get this to work with a single cell using code found in another question on this site:

<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

      // https://google,developers.appspot.com/chart/interactive/docs/spreadsheets#gid
      google.load('visualization', '1', {packages: ['corechart', 'line']});
      google.setOnLoadCallback(drawChart);

      function drawChart() {
        // Add your sheets url and range below
        var spreadsheetUrl = "https://your sheets url here?range=A1";
        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("test").innerHTML = dataTable.getValue(0, 0);
      }
    </script>
  </head>

  <body>
    <p id="test"></p>
  </body>
</html>

I am not sure how to expand this code to pull multiple cells to multiple ids for use in multiple <p> tags. I tried expanding it to multiple functions using the code below but with that code it ends up putting the data from B2 into the <p id="product2"> which should get the data from B3 and nothing in the <p id="product1"> which should get the B2 data.

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

      // https://google,developers.appspot.com/chart/interactive/docs/spreadsheets#gid
      google.load('visualization', '1', {packages: ['corechart', 'line']});
      google.setOnLoadCallback(drawChart);


   function drawChart() {
        // Add your sheets url and range below
        var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/1l6FmSuwU2E134UuxoNyRfvTw2UY_0G0q69ZwfbQy3mY/edit?range=B2";
        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("product1").innerHTML = dataTable.getValue(0, 0);
      }

      function drawChart2() {
        // Add your sheets url and range below
        var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/1l6FmSuwU2E134UuxoNyRfvTw2UY_0G0q69ZwfbQy3mY/edit?range=B3";
        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("product2").innerHTML = dataTable.getValue(0, 0);
      }
     </script>
</head>

<body>
<p id="product1" align="center"></p>
<p id="product2" align="center"></p>
</body>
Ad

Answer

dataTable.getValue(0, 0); gets the value at row 0, column 0 in current range which seems to be only B2.

First get the range required

   function drawChart() {
        // get B2:B10
        var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/1l6FmSuwU2E134UuxoNyRfvTw2UY_0G0q69ZwfbQy3mY/edit?range=B2:B10";
        var query = new google.visualization.Query(spreadsheetUrl);
        query.send(handleQueryResponse);
      }

Update as required. See range reference

Iterate over these values, and you can set the product descriptions as such:

  function handleQueryResponse(response) {
    var dataTable = response.getDataTable();
    // the rows and columns are 0 indexed
    // first row
    document.getElementById("product1").innerHTML = dataTable.getValue(0, 0);
    // second row
    document.getElementById("product2").innerHTML = dataTable.getValue(1, 0);
  }

you do not need to call drawChart2 as you do not need to fetch the data again, and you also do not need a second handleQueryResponse

Ad
source: stackoverflow.com
Ad