How to Correct Google Sheets' #REF Errors

Fixing #REF Errors in Google Sheets During Export

Google Apps Script Solution

function sendExcelAttachmentsInOneEmail() {
  var sheets = ['OH INV - B2B', 'OH INV - Acc', 'OH INV - B2C', 'B2B', 'ACC', 'B2C'];
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadSheetId = spreadSheet.getId();
  var urls = sheets.map(sheet => {
    var sheetId = spreadSheet.getSheetByName(sheet).getSheetId();
    return \`https://docs.google.com/spreadsheets/d/${spreadSheetId}/export?format=xlsx&gid=${sheetId}\`;
  });
  var reportName = spreadSheet.getSheetByName('IMEIS').getRange(1, 14).getValue();
  var params = {
    method: 'GET',
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };
  var fileNames = ['OH INV - B2B.xlsx', 'OH INV - Acc.xlsx', 'OH INV - B2C.xlsx', 'B2B.xlsx', 'ACC.xlsx', 'B2C.xlsx'];
  var blobs = urls.map((url, index) => {
    Utilities.sleep(10000);  // Delay added to avoid hitting rate limits
    var response = UrlFetchApp.fetch(url, params);
    return response.getBlob().setName(fileNames[index]);
  });
  var message = {
    to: 'email@domain.com',
    cc: 'email@domain.com',
    subject: 'Combined REPORTS - ' + reportName,
    body: "Hi Team,\n\nPlease find attached Reports.\n\nBest Regards!",
    attachments: blobs
  }
  MailApp.sendEmail(message);
}

Common Google Sheets Scripting Questions

1. Why does exporting data from Google Sheets to Excel result in the #REF error?
2. The #REF problem typically arises from Google Sheets references or formulas that are either unrecognized or incompatible with the formula environment in Excel.
3. How can I avoid using Google Apps Scripts and exceeding rate limits?
4. Using utilities to add script pauses.In order to control the frequency of queries and stay under Google's rate constraints, sleep (milliseconds) might be used.
5. In a URL fetch call, what does muteHttpExceptions accomplish?
6. If the HTTP request is unsuccessful, it permits the script to continue running without raising an exception, which is helpful for handling mistakes politely.
7. Can I change each sheet's file name when exporting to Excel?
8. Yes, as the script illustrates, you can give each blob that you convert from the sheet a unique name before connecting it to an email.
9. Is it possible to export Google Sheets to Excel directly without the need for middleware scripts?
10. It is possible to download a Google Sheet in Excel format manually from the File menu in Google Sheets; however, scripting is needed to make this process automatic.

Concluding Views on Sheet Export Difficulties

This investigation shows that although Google Apps Script offers strong tools for optimizing and automating Google Sheets features, there are specific challenges when integrating with other programs, such as Excel. When working with complicated queries and data references that are difficult to interpret outside of Google's ecosystem, #REF issues are a regular problem. Comprehending these constraints and incorporating them into scripts can greatly diminish their frequency, resulting in more seamless data management procedures.