Google Script - Take on array several cell values at same time-CodePudding

I've developed a script, but it is TOOOOO slow (>1min). I've read that less calls to take values reduces that time, so I'd ask how to do that. The idea is to take the cell values on just one single line, and then for each "let", take one part of that array.

Do you know how to do that? The code is this:

function createPDF() {
  
  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CRM");

  let profe_nombre = currentSheet.getRange(14,40).getValues();
  let profe_direccion = currentSheet.getRange(16,40).getValues();
  let profe_dni = currentSheet.getRange(15,40).getValues();
  let profe_iban = currentSheet.getRange(17,40).getValues();
  let profe_paypal = currentSheet.getRange(18,40).getValues();
  let profe_email = currentSheet.getRange(19,40).getValues();
  let factura_nombre = currentSheet.getRange(26,39).getValues();
  let factura_fecha = currentSheet.getRange(23,40).getDisplayValues();
  let factura_importe = currentSheet.getRange(22,40).getValues();
  let factura_notas = currentSheet.getRange(26,38).getValues();

  const docFile = DriveApp.getFileById("sheetID");
  const tempFolder = DriveApp.getFolderById("folderID");
  const pdfFolder = DriveApp.getFolderById("folderID");
  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody()
  body.replaceText("{profe_nombre}", profe_nombre);
  body.replaceText("{profe_direccion}", profe_direccion);
  body.replaceText("{profe_dni}", profe_dni);
  body.replaceText("{profe_iban}", profe_iban);
  body.replaceText("{profe_paypal}", profe_paypal);
  body.replaceText("{profe_email}", profe_email);
  body.replaceText("{factura_nombre}", factura_nombre);
  body.replaceText("{factura_fecha}", factura_fecha);
  body.replaceText("{factura_importe}", factura_importe);
  body.replaceText("{factura_notas}", factura_notas);
  tempDocFile.saveAndClose();
  const pdfContentBlob = tempFile.getAs(MimeType.PDF);
  pdfFolder.createFile(pdfContentBlob).setName(factura_nombre   ".pdf");
  tempFolder.removeFile(tempFile);
  SpreadsheetApp.getUi().alert("Factura creada");

}

function apendiceRemesa() {

  const SheetCRM = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CRM");
  const SheetRemesas = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Remesas");
  
  let remesa_cuenta = SheetCRM.getRange(17,40).getValue();
  let remesa_importe = SheetCRM.getRange(22,40).getValue();
  let remesa_nombre = SheetCRM.getRange(14,40).getValue();
  let remesa_concepto = SheetCRM.getRange(26,39).getValue();
  const remesa_estado_pago = SheetCRM.getRange(24,40).getValue();
  let remesa_estado_pago_fila = SheetCRM.getRange(23,43).getValue();
  let remesa_estado_pago_columna = SheetCRM.getRange(24,43).getValue();

  if (remesa_estado_pago == 'P') {
    SheetRemesas.appendRow([remesa_cuenta,remesa_importe,remesa_nombre,remesa_concepto]);
    SpreadsheetApp.getActiveSheet().getRange(remesa_estado_pago_fila, remesa_estado_pago_columna).setValue('RM');
    
  } else {
  SpreadsheetApp.getUi().alert('ERROR. Esta factura ya fue pagada')
  }
  

}

I've separated both functions because I have two different buttons for each, so it is not mandatory to first execute createPDF and then apendiceRemesa (I will develop a new function too for both functions, removing some redundant variables then).

Thank you!

CodePudding user response：

Solution:

First, retrieve a range containing all your desired values. A simple option would be to use getDataRange(), which will simply get a range corresponding to all sheet values.
Then, retrieve the different desired values from the resulting 2D array, using the corresponding indexes (first row index, then column index). It's important to note that arrays are 0-indexed, contrasting with the 1-indexed parameters from getRange, so you'll have to subtract 1 to each index.

Sample:

To show you how it's done, here's a sample that retrieves the first variables:

const values = currentSheet.getDataRange().getDisplayValues();
const profe_nombre = values[13][39];
const profe_direccion = values[15][39];
const profe_dni = values[14][39];
// ...and so on...

Note: If the cells were more contiguous with each other, I would recommend array destructuring to accomplish this with fewer lines of code.

CodePudding user response：

The trick is to get all data from the sheet as a 2d array and then get 'cells' from the array, not directly from the sheet.

Instead of this:

let profe_nombre    = currentSheet.getRange(14,40).getValues();
let profe_direccion = currentSheet.getRange(16,40).getValues();
let profe_dni       = currentSheet.getRange(15,40).getValues();
let profe_iban      = currentSheet.getRange(17,40).getValues();
let profe_paypal    = currentSheet.getRange(18,40).getValues();
let profe_email     = currentSheet.getRange(19,40).getValues();
let factura_nombre  = currentSheet.getRange(26,39).getValues();
let factura_fecha   = currentSheet.getRange(23,40).getDisplayValues();
let factura_importe = currentSheet.getRange(22,40).getValues();
let factura_notas   = currentSheet.getRange(26,38).getValues();

Try this:

const data = currentSheet.getDataRange().getDisplayValues(); // the 2d array

// take values from the array
let profe_nombre    = data[13][39];
let profe_direccion = data[15][39];
let profe_dni       = data[14][39];
let profe_iban      = data[16][39];
let profe_paypal    = data[17][39];
let profe_email     = data[18][39];
let factura_nombre  = data[25][38];
let factura_fecha   = data[22][39];
let factura_importe = data[21][39];
let factura_notas   = data[23][37];

Etc.