If you utilize data share with large datasets within Google Sheets, you may have seen this error:

This is an issue with Google's IMPORTDATA function, and in many cases it means you'll have to break up your import calls into smaller chunks, and then conglomerate them all. However, we can build a more dynamic way to get our Fulcrum data by using Google Apps Scripts.

Create a new sheet

Click on Extensions > Apps Script

Name the script, then copy + paste the code below into the Code.gs file.

// Add a menu item dedicated to running this function
function onOpen() {
  SpreadsheetApp.getUi() 
      .createMenu('Fulcrum Tools')
      .addItem('Import Fulcrum Data', 'showPrompt')
      .addToUi();
}

// Prompt for the data share URL
function showPrompt() {
  var ui = SpreadsheetApp.getUi(); 
  var result = ui.prompt(
      'Import Fulcrum App Data!',
      'Please enter the .csv URL of your data share or shared view:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();

  if (button == ui.Button.OK) {
    // User clicked "OK".
    // If the user enters a comma and number after the URL, the maximum 
    // number of rows will be the user's number times 1000.
    // Here, we need to split the array to separate the number from the URL.
    var paramArray = text.split(",");
    ui.alert('Getting data from ' + paramArray[0]);
    if (paramArray.length>1){ // If the user entered a comma and number after the URL...
      // Execute the function with a max of N x 1000 rows.
      importFulcrumData(paramArray[0],Number(paramArray[1]))
    } else{ // Otherwise, get up to 20,000 rows
      importFulcrumData(paramArray[0],20)
    }
  } else if (button == ui.Button.CANCEL || button == ui.Button.CLOSE) {
    // User clicked "Cancel".
    ui.alert('No URL entered');
  }
}

// Create the array formula that will import the Fulcrum data
function importFulcrumData(query, pages) { 
  var per_page=1000;
  // Get the current sheet and its A1 cell.
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');  
  var cell = sheet.getRange("A1"); 
  // Establish the URL and append parameters            
  var baseQuery = '"'+query + '?per_page=' + per_page + '&page=' ;
  // We need to wrap the IMPORTDATA array formula in a UNIQUE to make sure we
  // don't re-insert the column headers
  var eqStr = 'UNIQUE({';
  // create an IMPORTDATA function for each page in the total amount of pages
  // and add it to the array formula
  for( var i = 1; i<=pages; i++){
    eqStr += 'IMPORTDATA('+baseQuery + i + '")';
    if(i!=pages) eqStr += "; "
  }
  eqStr += '})';
  // Set A1 to our array formula
  cell.setFormula(eqStr);           
}

Save the code by clicking the 💾 icon. Then select the onOpen function from the function dropdown. Then click ▶️ Run.

You will be asked to review your permissions. All this does is link the Apps Script to your Google account so it can run on your Sheet. Neither I nor Fulcrum will be able to see, edit or delete your Sheets or Apps Scripts.

Once the script finishes running (which should only a take a few seconds) navigate back to your sheet, and click on the new menu item, Fulcrum Tools > Import Fulcrum Data

Enter in your data share or shared view .csv URL.

If you'd like to control the maximum number of rows returned from the function type a comma after your URL, followed by. a number that represents the number (in thousands) that you'd like to return. For example: https://web.fulcrumapp.com/shares/asdf1234asdf.csv, 14 will return a maximum of 14,000 rows. If you do not add a comma and number, then the maximum number of rows returned will be 20,000.

Click Ok and then the next OK

After a few moments, your sheet will populate with your data:

And that's it! 😎 🎉 🥳

Contact support@fulcrumapp.com if you have any other questions!