0

I am generating a pdf from a google sheet by first updating the F3 cell with a timestamp. The problem is that the PDF file is generated first, and then cell F3 is updated.

I am trying to do by code in google app script.

function updateAndExportSheet(e) {
  // ID of the sheet to update
  var sheetId = "sheet_id";

  // ID of the cell to update
  var cellId = "F3";

  // ID of the folder to save the PDF file in
  var folderId = "folder_ID";

  // Get the response date from the form
  var formResponse = e.response.getTimestamp().toString();

  // Get a reference to the sheet and update the cell with the form response
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName("Sheet1");
  sheet.getRange(cellId).setValue(formResponse);

  // Construct the URL to export the sheet to a PDF with horizontal orientation and 0.5 cm margins
  var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", sheet.getParent().getId());
  var url_ext = 'exportFormat=pdf&format=pdf' +
                '&size=A4' +
                '&portrait=false' +
                '&fitw=true&source=labnol' +
                '&sheetnames=false&printtitle=false' +
                '&pagenumbers=false&gridlines=false' +
                '&fzr=false' +
                '&top_margin=0.0&bottom_margin=0.0&left_margin=0.0&right_margin=0.0' +
                '&gid=';

  // Get the response for the sheet that can later be converted to a blob
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  // Save the PDF file to the specified folder
  var folder = DriveApp.getFolderById(folderId);
  folder.createFile(response.getBlob().setName("sheet.pdf"));
}

Kamil
  • 1
  • 1
    Welcome to Stackoverflow, you might find some help there : https://stackoverflow.com/questions/41175326/why-do-we-use-spreadsheetapp-flush – Waxim Corp Jan 05 '23 at 08:39

0 Answers0