2

I have a Google Spreadsheets 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 spreadsheet 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.

Screenshot

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

closed as off-topic by Rubén, serenesat, jonsca Apr 30 '18 at 22:07

  • This question does not appear to be about web applications within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the 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
  • 2
    I'm voting to close this question as off-topic because the Class UiApp used by the OP script is deprecated. – Rubén Apr 30 '18 at 18:57