0

We use a master spreadsheet containing all the information of the students. I want to create a UI to capture the marks of each student and write it to a Google Sheet from which I will generate their report cards.

I use the following code to import the data from the master list - the names gets imported correctly, but I cannot seem to pull the values? I just get "undefined"

/**
 * A function that inserts a custom menu when the spreadsheet opens to generate the Report Spreadsheet.
 */
function onOpen() {

  var menu = [{name: 'Capture Report Data', functionName: 'setUpProgressReport_'}];
  SpreadsheetApp.getActive().addMenu('Progress Report', menu);

}

/**
 * A set-up function that creates a Report Sheet based on the class selected
 */
function setUpProgressReport_() {

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Report 1');
  var ui = SpreadsheetApp.getUi(),
  response = ui.prompt(
    'Enter Class',
    'Please enter the class you would like to enter marks for',
    ui.ButtonSet.OK_CANCEL),
  selectedClass = response.getResponseText();

  //Import names of learners by selected class from Master Sheet

  var cell = sheet.getRange("A1");
  cell.setFormula('=QUERY(IMPORTRANGE("1Dxjt6W54e7n2F8a2zlRZV0n-VtCoPZTC2oZgeMPd8mE","MasterList!A1:Z2000"),"SELECT Col1, Col2,Col4 WHERE Col4 contains ' + "'" + selectedClass + "'" + ' Order by Col2 asc")');

  // Freezes the first row to be used as headings
  sheet.setFrozenRows(1);

  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var values = SpreadsheetApp.getActiveSheet().getRange(lastRow, lastColumn).getValues();
  Browser.msgBox(values[0][22]);
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Developer Primary
  • 1
  • 2
  • [1]Have you connected the spreadsheets? [2]Try "SELECT (A), (B), (D) WHERE (D) contains ' + "'" + selectedClass + "'" + ' Order by (B) asc")'); – Anees Hameed Mar 25 '16 at 18:14
  • The problem is not with importing the data, I can see the data in the spreadsheet and the query works. I just cannot access the values. It gives a value of "undefined" if I try to show the value of cell A15 for instance. Even though I can see the name in A15 is John. – Developer Primary Mar 26 '16 at 08:16
  • Related: http://stackoverflow.com/questions/27555729/google-spreadsheet-app-script-will-not-wait-for-results-to-load – Rubén Mar 28 '16 at 03:39

2 Answers2

0

Use SpreadsheetApp.flush() to apply all pending spreadsheet changes before getting the values of cells previously modified by the script.

From https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#flush()

Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.

Also could be helpful to include a test loop to be sure that the IMPORTRANGE task is complete. This test loop could check every certain amount of time,let say 500 millisecondes if certain change already occurred, in example, the script could get the last row before doing the import and compare it with the last row after it and doing a loop until the last is greater than the first.

An alternative is to use Utilities.sleep(milliseconds) alone. This could work but since the IMPORTRANGE execution time isn't deterministic we can not know for sure how many time is required.

Rubén
  • 34,714
  • 9
  • 70
  • 166
0

I am no expert but I think I kind of figured out what the problem was... well in theory and maybe not in the correct technical details.

var ss = SpreadsheetApp.getActive() sets the current spreadsheet as the ss value, and this is without the imported data. So referencing this variable actually references the data before it was imported. By creating a seperate function and "refreshing" the var ss = SpreadsheetApp.getActive() solved the issue and I could retrieve data normally.

Developer Primary
  • 1
  • 2