воскресенье, 4 сентября 2016 г.

How do I pull text from multiple cells in a google spreadsheet to website each in a different

tag?

06:57 By , , No comments


up vote0down votefavorite
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>
  
shareimprove this question
up vote1down voteaccepted
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
shareimprove this answer
   
Doing that brought the correct input back to "product1" but gives nothing in "product2". (There is test data in those cells) Does the fact that I put in the URL with range=B2 have anything to do with that? You answered that as I was testing/asking the question :D. – Derrick Potter Jan 24 at 0:54 
   
oh yes, sorry, my mistake, didn't notice the range restriction. Updated answer to extend range – mani Jan 24 at 0:55
1 
You sir, are my hero. So simple... I always try to make these things more complicated. Thanks a million! – Derrick Potter Jan 24 at 0:57
   
you're welcome =) – mani Jan 24 at 0:57

Your Answer

0 коммент.:

Отправить комментарий