CODEWITHSUNDEEP
google-apps-script
Dmitriy Rudakov
Dmitriy Rudakov

Reputation: 109

Apps script export data to another spreadsheet, build it in xlsx and attach it to email

I have the problem with one project. The task is:

  1. Export data from one spreadsheet to second spreadsheet - function expData
  2. Rebuild second spreadsheet to xlsx-format - function getGoogleSpreadsheetAsExcel
  3. Send email with xlsx-attachment some receivers - function getGoogleSpreadsheetAsExcel

var url = SpreadsheetApp.getActiveSpreadsheet().getUrl();
var url1 = "https://docs.google.com/spreadsheets/d/16W0E_FCW-C_1ZVV_BOQgSAdT_MhRZs-KOQhYsRcZi6o/edit?usp=sharing";
let sprShIn = SpreadsheetApp.openByUrl(url1);

function expData() {
  
  let url = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  let sprShOut = SpreadsheetApp.openByUrl(url);
  let shOut = sprShOut.getSheetByName("Сводная таблица 1");
  let lRowOut =  shOut.getRange("A6:A").getValues().filter(String).length;
  let data = shOut.getRange('A6:E' + (lRowOut + 5)).getValues(); //assign the range you want to copy
    
  let shIn = sprShIn.getSheetByName('fXLSX');

  let lRowIn =  shIn.getRange("A1:A").getValues().filter(String).length;
  shIn.deleteRows(2, lRowIn);
  lRowIn =  shIn.getRange("A1:A").getValues().filter(String).length;
  shIn.getRange(lRowIn + 1, 1, lRowOut,shIn.getLastColumn()).setValues(data); 

}

function getGoogleSpreadsheetAsExcel(){

  try {

    let sprShOut = SpreadsheetApp.openByUrl(url);
    //let ss = SpreadsheetApp.openByUrl(url1);
    let url2 = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + sprShIn.getId() + "&exportFormat=xlsx";
    let params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    let blob = UrlFetchApp.fetch(url2, params).getBlob();
    let datetimeMsg = new Date().toLocaleString();

    blob.setName(datetimeMsg + "_JSON.xlsx");

  let shOut = sprShOut.getSheetByName("адреса для рассылки");
  let rowsAmount = shOut.getRange("A1:A").getValues().filter(String).length;
  let dataRange = shOut.getRange(1, 1, rowsAmount, 1);
  let data = dataRange.getValues();
  let emailAddress = "";

    for (i in data) {
      var row = data[i];
      // Из первого столбца берем адрес
      if (emailAddress == "") {emailAddress = row[0];}
      else {emailAddress = emailAddress + ", " + row[0];}
    }

    MailApp.sendEmail(emailAddress, "Google Sheet to Excel", "The XLSX file is attached", {attachments: [blob]});

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

function doJOB(){
  expData();
  Utilities.sleep(5000); //trying make timeout
  getGoogleSpreadsheetAsExcel();
}

If I start both functions one after another separately, everything works good, data goes to another spreadsheet, another spreadsheet builds to xlsx-format, mail sends with xlsx-attachment.

But if I start function doJOB, data does not have time to be exported as the letter is already sent with an empty xlsx-file. I was trying to enter timeout with different milliseconds but it did not help. What's wrong? Thank you for help!

Upvotes: 1

Views: 225

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I thought that in this case, in order to reflect the change of the sheet of "fXLSX" in SpreadsheetApp.openByUrl(url1), I would like to propose the following modification in the function of doJOB().

From:

Utilities.sleep(5000);

To:

SpreadsheetApp.flush();

Reference:

Upvotes: 1

Related Questions