Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – How can i set this for each NEW row

I’m trying to set up this in Google Sheets, where there’s info being added to a spreadsheet and a pdf is created from a template. I copied this code from a YT video:

function createBulkPDFs(){
  const docFile = DriveApp.getFileById("ID");
  const tempFolder = DriveApp.getFolderById("ID");
  const pdfFolder = DriveApp.getFolderById("ID");
  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja 1");
  const data = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 24).getValues(); 
  let errors = [];
  data.forEach(row => {
    try{
    createPDF(row[2], row[3], row[4], row[8], row[9], row[16], row[17], row[18], row[19],"manny"+row[2], docFile, tempFolder, pdfFolder);
    errors.push([""]);
    }catch(err){
      errors.push(["Failed"]);
    }

});
  currentSheet.getRange(2, 26,currentSheet.getLastRow()-1, 1).setValues(errors);


}

function createPDF(cliente, cotizacion, atencion, ciudad, fecha, manoDeObra, traslados, materiales, total, pdfName, docFile, tempFolder, pdfFolder) {

  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody();

  body.replaceText("{Cliente}", cliente);
  body.replaceText("{Cotizacion}", cotizacion);
  body.replaceText("{Atencion}", atencion);
  body.replaceText("{Ciudad}", ciudad);
  body.replaceText("{Fecha}", fecha);
  body.replaceText("{ManoDeObra}", manoDeObra);
  body.replaceText("{Traslados}", traslados);
  body.replaceText("{Material}", materiales);
  body.replaceText("{Total}", total);

  tempDocFile.saveAndClose();
  const pdfContentBlob = tempFile.getAs(MimeType.PDF);
  pdfFolder.createFile(pdfContentBlob).setName(pdfName);
  tempFolder.removeFile(tempFile);

}

But this creates a new PDF for every single row everytime a new one is added, creating tons of duplicates, how can i set it up so just the new one is created?

I guess this is the line that is responsible for that but i dont know how to change it
data.forEach(row => { try{ createPDF(row[2], row[3], row[4], row[8], row[9], row[16], row[17], row[18], row[19],"manny"+row[2], docFile, tempFolder, pdfFolder); errors.push([""]); }catch(err){ errors.push(["Failed"]); }

Tags:

2

Answers


  1. 0

    It depends on how the new rows are added.

    The simplest option might be to run the script only for the row with the current cell highlighted. This cell has a bolder border than the other highlighted cells.

    The following function returns the active sheet, the row number of the current cell, and the row values. Disclaimer: I have yet to test it.

    function getCurrentRow(){
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const rowNumber = activeSheet.getCurrentCell().getRow();
  const [ row ] = activeSheet.getRange(rowNumber, 1, 1, 24).getValues();
  return [
    activeSheet, 
    rowNumber,
    row
  ];
}

    The function is to create a PDF for the row having the current cell.

    function createPDFforCurrentRow(){
  // from the original code
  const docFile = DriveApp.getFileById("ID");
  const tempFolder = DriveApp.getFolderById("ID");
  const pdfFolder = DriveApp.getFolderById("ID");
  let result;
  // New codelines
  const [activeSheet, rowNumber, row] = getCurrentRow();
  // Adapted from the original code 
  try {
    createPDF(row[2], row[3], row[4], row[8], row[9], row[16], row[17], row[18], row[19],"manny"+row[2], docFile, tempFolder, pdfFolder);
    result = 'Success';
  } catch(error) {
    result = 'Failure';
  }
  activeSheet.getRange(rowNumber, 26).setValue(result);
}

    Among the other options, you might use a trigger. Google Apps Script has two trigger categories: simple and installable. If the rows are inserted by a Google Forms submission, you might use one of the form submit triggers. The most convenient might be the corresponding spreadsheet rather than the one corresponding to a form.

    Login or Signup to reply.
  2. 0

    You could check if the file already exists, by using DriveApp.getFileById('file_name').

    Then, call your createPDF function if the result is false.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search