2

I've put together a web application that relies on a series of Google components.

  • Google Forms collects data from users
  • Google sheets saves the data (automatically)
  • Additional "calculated" columns get populated based on data collected from the forms
  • I query the "calculated" columns and display the results on a webpage (intranet) using a Table from the Google Visualizations api.

The problem

The query sometimes returns the message "loading..." for one of the calculated columns instead of the calculated value. This particular column is unique in that it calls a custom function that I have defined for the spreadsheet. Refreshing the page seems to correct this problem (and the calculated value is displayed), but the "loading..." appears roughly 1 out 3 times that I make a unique visit to the page.

Looking for

I'm hoping that I can stop this from happening or at least minimize the occurrences. My first thought is to put a delay in the javascript somewhere to give the calculation a longer time to complete, but I'm not sure how. Can you show me? Any help is appreciated.

Code pieces

Google Sheets Formula for that column

=ARRAYFORMULA(IF(English_Merged_Data!A2:A ="","","<strong><a target='_blank' href='https://docs.google.com/forms/d/.../viewform?entry.500003066="&encode(English_Merged_Data!Q2:Q)&"'>Apply</a></strong>"))

Encode custom function (in Sheets)

//This function encodes a single string or an array of strings
function encode(value) {
  if( typeof value === 'string' ) {
    return encodeURIComponent(value);
  }

  var encodedArray = [];
  for(var i=0;i<value.length;i++){
    encodedArray.push(encodeURIComponent(value[i]).replace("'","%27"));
  }
  return encodedArray;
}

Javascript to display table

<script type="text/javascript">
 google.load("visualization", '1', {packages:['table']});
 google.setOnLoadCallback(drawTable);

  function drawTable() {
      var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheets/d/.../edit?sheet=English_Display_Table'); 
      query.setQuery("select A, B, C, D, F, H, G where A <> '' and not I contains 'Close' and J > toDate(now()) label A 'Mission', B 'Category', C 'Language', D 'Location', F 'Time Commitment', G 'For more details', H 'Apply!'");
      query.send(handleQueryResponse);
  }

function handleQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var data = response.getDataTable();

  var table = new google.visualization.Table(document.getElementById('table_div'));

  table.draw(data, {showRowNumber: false, allowHtml: true});
}
</script>
  • 1
    You can insert a delay with Utilities.sleep(...). Other than that, it'd be nice to have a minimal self-contained example for reproducing the error; e.g., the data visualization script is probably unnecessary if the "loading" message appears at an earlier stage of processing. – user79865 Jul 22 '15 at 21:11
  • 1
    Try changing English_Merged_Data!A2:A and English_Merged_Data!Q2:Q by a smaller ranges. If the size is variable use offset() with another function to calculate the number of required rows. Consider to share a file with representative data. – Rubén Jul 22 '15 at 23:39
  • 1
    I've played with the ranges as @Rubén suggests, and I can see that the custom function call is just REALLY slow, even for a single cell. @NormalHuman you're right, this does appear to be a much simpler problem. Here's a minimal example: docs.google.com/spreadsheets/d/… When I copy/paste A1 into new cells, it takes a couple seconds before the "loading..." goes away and is replaced by the result. Is there any way to speed it up? If not I'll try the Utilities.sleep() – grayob Jul 23 '15 at 14:56
  • 1
    If you want to perform the same action on many cells with a script, don't do it cell-by-cell. Have one script process the entire range. It will not be a custom formula anymore, just a script invoked manually, e.g., from a menu. And I second Rubén's request. – user79865 Jul 23 '15 at 20:51
  • 1
    Instead of a custom function, you can use REGEXREPLACE to achieve the same substitution that your custom function does. This can be applied to the entire column with ARRAYFORMULA, so the whole thing could be done with ARRAYFORMULA(REGEXREPLACE(A2:A , "'", "%27")) – user79865 Jul 23 '15 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.