Send contents of Google Sheet when one cell is not empty

I'm trying to write a script that sends the content of one sheet when one particular cell is not empty. The checking part of the script is working. The email part is working too. I would like to attach the contents of the sheet (ideally in xlsx format, but csv could do if it is simpler) to the email but all the scripts I found online seem to use some Google Drive API and haven't figured out how to use it.

I commented out the lines that should be doing the exporting and, for now, the script just sends a reminder to manually export the sheet. This I would like to automate. Also, instead of sending the email to a hard-coded email, I would like to send it to the email address found in a particular cell in the same sheet (I1).

Any help would be appreciated.

Here's my script:

unction checkAndExport() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'Bookings List'; // Replace with the desired sheet name

  var sheet = spreadsheet.getSheetByName(sheetName);
  var checkCell = sheet.getRange('B3'); // Cell to check if it is empty
  var cellValue = checkCell.getValue();

  if (cellValue !== '') {
    // Export sheet data to a CSV string
  //  var outputDocument = DocumentApp.create('My custom csv file name'); 
  //  var content = getCsv();
  //  var textContent = ContentService.createTextOutput(content);
  //  textContent.setMimeType(ContentService.MimeType.CSV);
  //  var blob = Utilities.newBlob(textContent, 'text/csv', sheetName + '.csv');

    // Send an email with the CSV file attached
    var recipientEmail = 'name@domain.com'; // Replace with actual email address
    var subject = 'Bookings Email reminder';
    var body = 'It is time to send the bookings list to some leader!';

    var options = {
      name: 'Exported Sheet',
    //  mimeType: blob.getContentType(),
      inline: false,
    //  attachments: [blob]
    };

From your question, I couldn't understand the source you want to attach as XLSX data. So, this is my guess. In order to attach the specific sheet in the active Spreadsheet as XLSX data to email, how about the following modification?

Modified script:

function checkAndExport() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'Bookings List'; // Replace with the desired sheet name

  var sheet = spreadsheet.getSheetByName(sheetName);
  var checkCell = sheet.getRange('B3'); // Cell to check if it is empty
  var cellValue = checkCell.getValue();

  if (cellValue !== '') {
    // --- I modified the below script.
    // Retrieve XLSX data from the specific sheet of the active Spreadsheet as blob.
    var format = "xlsx"; // or "csv"
    var sheetId = sheet.getSheetId(); // or spreadsheet.getSheetByName("sheet name").getSheetId()
    var blob = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/export?id=${spreadsheet.getId()}&exportFormat=${format}&gid=${sheetId}`, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();

    // Send an email with the CSV file attached
    var recipientEmail = sheet.getRange("I1").getDisplayValue();
    var subject = 'Bookings Email reminder';
    var body = 'It is time to send the bookings list to some leader!';

    MailApp.sendEmail({ to: recipientEmail, subject, body, name: 'Exported Sheet', attachments: [blob] });
  }
}

• When this script is run, the "Bookings List" sheet in the active Spreadsheet is attached to the email as XLSX data. If you want to change the sheet, please modify var sheetId = sheet.getSheetId(); to var sheetId = spreadsheet.getSheetByName("sheet name").getSheetId();.
• If you want to convert the sheet to CSV, please modify var format = "xlsx"; to var format = "csv";.
• The email address is retrieved from "I1" of the "Bookings List" sheet. This is from Also, instead of sending the email to a hard-coded email, I would like to send it to the email address found in a particular cell in the same sheet (I1)..

Note:

• In your script, the script for sending email is not included. So, I added it using MailApp.sendEmail as a sample. So, please modify it to your actual situation.

Reference:

• sendEmail(message) of Class MailApp

Added:

About your following request,

I noticed however that, since the values of three cells are formulas dependent on other sheets in the same file, the exported file shows an error in these cells (B1, I1 and O1). Would it be possible to export "values only"?

In this case, how about the following sample script?

function checkAndExport() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'Bookings List'; // Replace with the desired sheet name

  var sheet = spreadsheet.getSheetByName(sheetName);
  var checkCell = sheet.getRange('B3'); // Cell to check if it is empty
  var cellValue = checkCell.getValue();

  if (cellValue !== '') {
    // --- I modified the below script.
    // Retrieve XLSX data from the specific sheet of the active Spreadsheet as blob.
    var format = "xlsx"; // or "csv"
    var newSheet = sheet.copyTo(spreadsheet).setName(`new_${sheetName}`);
    var range = newSheet.getDataRange();
    range.copyTo(range, { contentsOnly: true });
    var sheetId = newSheet.getSheetId(); // or spreadsheet.getSheetByName("sheet name").getSheetId()
    SpreadsheetApp.flush();
    var blob = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/export?id=${spreadsheet.getId()}&exportFormat=${format}&gid=${sheetId}`, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();

    // Send an email with the CSV file attached
    var recipientEmail = sheet.getRange("I1").getDisplayValue();
    var subject = 'Bookings Email reminder';
    var body = 'It is time to send the bookings list to some leader!';

    MailApp.sendEmail({ to: recipientEmail, subject, body, name: 'Exported Sheet', attachments: [blob] });

    spreadsheet.deleteSheet(newSheet);
  }
}