Data Source Excel replacement doesn't work for large files

[sferabolli]

I have a dashboard with 2 data sources:

Source A - is smaller (300 rows, 31kb), used in a blend as an outer join. Whenever I go to added Data Sources and replace the smaller one with its newest version (same name, same format, etc), it allows me to replace no problem, and the same data source updates.

Source B - larger, contains transactional data (400k rows, 40mb). When I try to do the exact same thing with the large set, the replace source process never finishes, leaving me with the old version always. 

I have tried a bunch of things: removing and re-adding the source, clearing cache, doing outside company VPN, nothing seems to work.

Is this issue known? Does anyone knows how to solve it?

tyvm!

[Sichali1]

Hi,

It seems like you're having trouble replacing the larger data source (Source B) in your dashboard, while Source A works just fine. The issue might be due to the size of Source B (400k rows and 40MB), which could be causing the replacement process to time out or fail because it's too large for the system to handle efficiently.

One thing you could try is switching from Excel to Google Sheets. Google Sheets handles larger datasets better than Excel, as it's designed to work more efficiently with cloud-based tools and can manage big datasets without slowing down.

Additionally, Google Sheets allows real-time updates, so you don’t need to replace the entire source every time there’s a change; updates happen automatically. This makes syncing your data easier and eliminates the need for manual uploads.

To get you started, upload your data source files to Google Drive and use the script below to convert Excel to Sheets. Kindly note that you could manually sync the data or automate the process to whatever interval you wish, whether hourly, daily, etc.

Here’s a script that can convert an Excel file to a Google Sheet:

function syncExcelToSheet() {

  var fileId = 'YOUR_EXCEL_FILE_ID';

  var sheetId = 'YOUR_GOOGLE_SHEET_ID';

  var sheetName = 'Sheet1';

  var file = DriveApp.getFileById(fileId);

  var blob = file.getBlob();

  var tempSheet = SpreadsheetApp.create('TempSheet');

  var tempSheetId = tempSheet.getId();

  Drive.Files.insert({

    title: 'TempSheet',

    mimeType: MimeType.GOOGLE_SHEETS

  }, blob, {convert: true});

  var tempSpreadsheet = SpreadsheetApp.openById(tempSheetId);

  var tempSheet = tempSpreadsheet.getSheets()[0];

  var targetSpreadsheet = SpreadsheetApp.openById(sheetId);

  var targetSheet = targetSpreadsheet.getSheetByName(sheetName);

  targetSheet.clear();

  var data = tempSheet.getDataRange().getValues();

  targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);

  DriveApp.getFileById(tempSheetId).setTrashed(true);

  Logger.log('Data synced from Excel to Google Sheet successfully!');

}

function createTrigger() {

  ScriptApp.newTrigger('syncExcelToSheet')

           .timeBased()

           .everyHours(1)

           .create();

}

[sferabolli]

Appreciate the answer! I'll try this and come back with updates.