CODEWITHSUNDEEP
htmlgoogle-apps-scriptweb-applicationsgoogle-sheets
jose m ruiz
jose m ruiz

Reputation: 37

Get all data from spreadsheet into html

When I run this as a Google script (within code.gs) it works, however, when I take it to the index.html file I can't get the whole data from the spreadsheet.

sheets should be 3D, sheets[0] should show all the data in sheet 0 (rows x cols) But instead in data, I get what it looks like a string (when I do data[0] it shows only the first character). Why?

Here is the code:

GAS

function doGet(request) {
  return HtmlService.createTemplateFromFile('index')
    .evaluate();
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
    .getContent();
}

function getSheetsData(){
  ss = SpreadsheetApp.openById('id_key');         
  var sheets = []; 
  for (var i = 0; i < ss.getSheets().length; ++i) {           
    sheets.push(ss.getSheets()[i].getDataRange().getDisplayValues());  
  }    
  return (sheets)
}

CLIENT SIDE

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">    
    <?!= include('css'); ?>    

    <!-- the idea is to pull the data from the spreadsheet and build slides with the questions to show a Quiz of questions (not yet done)-->
    <script>
      var datas = null;
      google.script.run.withSuccessHandler(function(result){ 
        datas = result;
        console.log(datas); //YES, datas has the data
      }).getSheetsData();
      console.log(datas);   //NO, datas is null
    </script>         
    </head>

  <body>    
  <h1>TESTS QUIZ [WEB APP]
    <br>
    <br>
      <!-- Build select options from spreadsheet sheets -->
      <?  var ss = SpreadsheetApp.openById('id_key'); ?>
      <select class="classic" id="sheet" onchange="someFunctionWillGoHereToStartTheQuiz">
      <option value="" disabled selected style="display:none;">Choose Quiz</option>
      <? for (var i = 0; i < ss.getSheets().length; ++i) { ?>      
        <option value="<?=i?>"><?= ss.getSheets()[i].getSheetName() ?></option>   
      <? } ?>
      </select>  
      <br>
      <br>
  </h1>
  <!--some code will go here to show the questions in the page-->

Upvotes: 2

Views: 2171

Answers (1)

JSmith
JSmith

Reputation: 4810

One suggestion would be to use this method.

  1. create a function server-side to get all datas
  2. retrieve datas client -side using success handler and calling google.script.run.myFunction()

GAS

function getSheetsData(){
 ss = SpreadsheetApp.openById('id_key_spreadsheet');         
    var sheets = []; //except if you want to add more datas no need to declare a 3D array
    for (var i = 0; i < ss.getSheets().length; ++i) {
       // I'v corrected this part so you don't get any "out of bounds" errors
      sheets.push(ss.getSheets()[i].getDataRange().getDisplayValues());  
    }
  return (sheets)
}

client side

<script>
 var datas = null;
 google.script.run.withSuccessHandler(function(result)
   { 
     datas = result;
   }).getSheetsData();
</script>

now if you want to grab the first string consider using 2 times [] as so datas[0][0] as you are using 2D array. Your problem also might come from here.

REFERENCES

google.script.run

Upvotes: 2

Related Questions