Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a Google Spreadsheet with a couple of sheets that get data from other spreadsheets via ImportRange function and then calculate numeric values based on that data. Then, I assembled a code snippet as web app to retrieve the first sheet of the spreadsheet and show it as a flex table.

Besides the doGet function, I added a "refresher" to retrieve the data from the spreadsheet, because when the Ss is closed, no calculations are performed, and the script would only read empty cells.

However, even though the "refresher" is in place, it shows only #VALUE! where the numbers should display.

    function doGet(){
  var app = UiApp.createApplication();
  var panel = app.createVerticalPanel();
  var flexTable = app.createFlexTable().setStyleAttribute('border', '3px solid MidnightBlue')
      .setStyleAttribute('borderCollapse','collapse')
      .setBorderWidth(2);
      //Get Data from spreadsheet
  var spreadsheetId = 'xxxx.xxx.xxxx';
  var dataArray = getData(spreadsheetId);
 //Load data into table cells 
  for (var row = 0; row<dataArray.length; row++){
    for (var col = 0; col<dataArray[row].length; col++){
      flexTable.setText(row, col, dataArray[row][col].toString());
    }
  }  
  panel.add(flexTable);
  app.add(panel);
  return app;
}

function refreshSheet(spreadsheetId, sheet) {
  var spreadsheetId = SpreadsheetApp.openById('xxxxxxx.xxxxxxxx');
  var sheet = spreadsheetId.getSheetByName('Sheet_to_Display');
  var dataArrayRange = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
  var dataArray = dataArrayRange.getValues(); // necessary to refresh custom functions
  var notValueFound = true;
  while(notValueFound) {
    for(var i = 0; i < dataArray.length; i++) {
      if(dataArray[i].indexOf('#VALUE!') >= 0) {
        notValueFound = true;
        dataArray = dataArrayRange.getValues();
        break;
      } // end if
      else if(i == dataArray.length - 1) notValueFound = false;
    } // end for
  } // end while
}

function getData (spreadsheetId){
 var ss = SpreadsheetApp.openById(spreadsheetId);
  var sheet = ss.getSheets()[0].getDataRange();
  return sheet.getValues();
}

I'm adding an actual screenshot to further illustrate the issue. The alphanumeric values that appear are "native" to the spreadsheet, and the values that do not, are coming from other spreadsheet via importRange function. The script is placed in the "google site root", not inside the spreadsheet.

enter image description here

I'm not sure where to fix my code, please give me a hint!

Thanks!

share|improve this question
    
Try flexTable.setWidget(row, col, app.createTextBox().setText(dataArray[row][col].toString()));. Btw, I don't see how your code can build a flex table like this. Welcome to Web Applications Gabriel !! –  Jacob Jan Tuinstra Oct 29 '13 at 10:54
    
How is the refreshSheet function invoked, with a button, trigger? –  Jacob Jan Tuinstra Oct 29 '13 at 11:04
    
@JacobJanTuinstra: Thanks for the welcoming! I tried flexTable.setWidget(row, col, app.createTextBox().setText(dataArray[row][col].toString())); and it makes the columns with a fixed width, and there are just too many to fit the webpage. However, the style is neat and very html-stylish. Is there a way to tell the flextable to adjust the width to the extent of the text only? –  Gabriel Crivelli Oct 29 '13 at 11:16
    
@JacobJanTuinstra: I'm setting a 1-hour trigger to the function refreshSheet to test it's behaviour... Thanks for asking that! –  Gabriel Crivelli Oct 29 '13 at 11:20
    
I've learned something today Gabriel: flexTable.setText(row, col, data.toString()); will create a flex table !! –  Jacob Jan Tuinstra Oct 29 '13 at 14:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.