0

I have a Google Sheet that acts a reusable sheet. My App Script (ideally) generates data on the Google Sheet, exports it as an Excel file, then clears the sheet for the next use. The issue is that the exported file seems to be the contents of the the Google Sheet BEFORE the Google Sheet is updated/created. For example, if the Google Sheet starts off empty, and then I write/edit the Google Sheet so that it contains data, the emailed Excel file will be empty. (If the Google Sheet starts off with certain data (Data_1) and then new data replaces it(Data_2), the Excel file includes Data_1.) I tried using Utilities.sleep(2000), but it did not work. For testing purposes I have omitted the code that clears the Google Sheet below. (I have been manually deleting instead).

I am opening the spreadsheet again by using SpreadsheetApp.openById("the_ID") after the edits are made, so I am not sure why it is exporting any older version of the Google Sheet. Does anyone know why this is happening, and how I could fix it? Thank you in advance!

var bss = SpreadsheetApp.openById("the_ID");
var billing_sheet = bss.getSheets()[0]; 

function Main(){

  Loop_through_Billing_List(); // This function creates the data on the Google Sheet 
  Utilities.sleep(2000); 
  getGoogleSpreadsheetAsExcel(); // This exports the Google Sheet as an Excel file in an email

}



function getGoogleSpreadsheetAsExcel(){

  try {

    var attemp = SpreadsheetApp.openById("the_ID"); //same ID in bss

     var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + attemp.getId() + "&exportFormat=xlsx";

    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();

    blob.setName(bss.getName() + ".xlsx");

    MailApp.sendEmail(requestor_email, "Billing " + FormatDate(date_start) + " - " + FormatDate(date_end), "The XLSX file is attached", {attachments: [blob]}); 
  
  

  } catch (f) {  
    Logger.log(f.toString()); 
  }
} 

getGoogleSpreadsheetAsExcel() CAME FROM: https://www.labnol.org/code/20124-convert-google-spreadsheet-to-excel Slight additions were made

business
  • 15
  • 2
  • 1
    In your situation, how about replacing `Utilities.sleep(2000); ` with `SpreadsheetApp.flush();`? [Ref](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#flush) – Tanaike May 28 '21 at 23:59
  • 1
    I thought that these threads will be also useful. https://stackoverflow.com/q/34753860/ https://stackoverflow.com/q/64020297/ – Tanaike May 29 '21 at 00:06

0 Answers0