Public Pastes
daily pastebin goal
41%
SHARE
TWEET

Untitled

a guest Jan 29th, 2018 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. function guardar() {
  3.   var sheet = SpreadsheetApp.getActiveSpreadsheet();
  4.   sheetName = sheet.getSheetName();
  5.  
  6. //  if(sheetName == 'Piédica")  
  7. //  else if (sheetName == "Sensor Medica")
  8.  
  9.  
  10.   codigo = sheet.getRange("D12").getValue();
  11.   idCliente = sheet.getRange("E16").getValue();
  12.   nombreCliente = sheet.getRange("B17").getValue();
  13.   fechaEnvio = sheet.getRange("B14").getValue();
  14.   moneda = sheet.getRange("F12").getValue();
  15.   cambio = sheet.getRange("F14").getValue();
  16.   total = sheet.getRange("I47").getValue();
  17.  
  18.   carpetaClienteId = crearCarpetaCliente(nombreCliente);
  19.  
  20.   fileUrl = clonarSpreadsheet(sheet, carpetaClienteId, sheetName);
  21.  
  22.   agregarListaCotizaciones(sheet, sheetName, codigo, idCliente, nombreCliente, fechaEnvio, moneda, cambio, total, fileUrl);
  23. }
  24.  
  25.  
  26.  
  27.  
  28. function crearCarpetaCliente(nombreCliente) {
  29.  
  30.   if (nombreCliente == '')
  31.     return false;
  32.  
  33.   carpetaCotizaciones = DriveApp.getFolderById(carpetaCotizacionesId);
  34.   carpetaClienteNombre = nombreCliente;
  35.  
  36.   try {
  37.     var carpetaCliente = carpetaCotizaciones.getFoldersByName(carpetaClienteNombre).next();
  38.   }
  39.   catch(e) {
  40.     var carpetaCliente = carpetaCotizaciones.createFolder(carpetaClienteNombre);
  41.   }
  42.  
  43.   return carpetaCliente.getId();
  44.  
  45. }
  46.  
  47. function clonarSpreadsheet(sheet, carpetaClienteId, sheetName) {
  48.   var range = sheet.getRange('A1:I64');
  49.   var data = range.getValues();
  50.   var bgColors = range.getBackgrounds();
  51.   var fontColors = range.getFontColors();
  52.   var fontFamilies = range.getFontFamilies();
  53.   var fontLines = range.getFontLines();
  54.   var fontSizes = range.getFontSizes();
  55.   var fontStyles = range.getFontStyles();
  56.   var fontWeights = range.getFontWeights();
  57.   var hAlignments = range.getHorizontalAlignments();
  58.   var vAlignments = range.getVerticalAlignments();
  59.   var mergedRanges = range.getMergedRanges();
  60.   var wraps = range.getWraps();
  61.  
  62.   var now = new Date();
  63.   formattedDate = Utilities.formatDate(now, "GMT-6", "dd-MM-yyyy HH:mm:ss");
  64.   fileName = sheetName + ' - ' + formattedDate;
  65.  
  66.   tmpFileId = SpreadsheetApp.create(fileName).getId();
  67.   newFileId = moveFiles(tmpFileId, carpetaClienteId);
  68.  
  69.   var newSs = SpreadsheetApp.openById(newFileId); //replace with destination ID
  70.   var newSheet = newSs.getActiveSheet();
  71.  
  72.   newRange = newSheet.getRange('A1:I64');
  73.   newRange.setValues(data);
  74.   newRange.setBackgrounds(bgColors);
  75.   newRange.setFontColors(fontColors);
  76.   newRange.setFontFamilies(fontFamilies);
  77.   newRange.setFontLines(fontLines);
  78.   newRange.setFontSizes(fontSizes);
  79.   newRange.setFontStyles(fontStyles);
  80.   newRange.setFontWeights(fontWeights);
  81.   newRange.setHorizontalAlignments(hAlignments);
  82.   newRange.setVerticalAlignments(vAlignments);
  83.   newRange.setWraps(wraps);
  84.  
  85.   //merged cells
  86.   for (var i = 0; i < mergedRanges.length; i++) {
  87.     tempRange = newSheet.getRange(mergedRanges[i].getA1Notation());
  88.     tempRange.merge();
  89.     tempRange.setValue(mergedRanges[i].getDisplayValue());
  90.   }
  91.  
  92.   //Alto y ancho de celdas
  93.   lastrow = range.getNumRows();
  94.   lastcol = range.getNumColumns();
  95.   for (var i = 1; i <= lastrow; i++) {
  96.     newSheet.setRowHeight(i, sheet.getRowHeight(i));
  97.   }
  98.   for (var i = 1; i <= lastcol; i++) {
  99.     newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
  100.   }
  101.   //Quita salto de linea de textos
  102.   newSheet.getRange('A1:I64').setWrap(false);
  103.   //Lineas del documento
  104.   newSheet.getRange('B9').setBorder(false, false, true, false, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  105.   newSheet.getRange('B15:I15').setBorder(false, false, true, false, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  106.   newSheet.getRange('B18:I18').setBorder(false, false, true, false, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  107.   newSheet.getRange('B30:I30').setBorder(false, false, true, false, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  108.   newSheet.getRange('B51:I51').setBorder(false, false, true, false, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  109.   newSheet.getRange('B54:I54').setBorder(false, false, true, false, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  110.   newSheet.getRange('B59:I59').setBorder(false, false, true, false, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  111.   newSheet.getRange('B63:I63').setBorder(false, false, true, false, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  112.   newSheet.getRange('B44:G47').setBorder(true, true, true, true, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  113.   newSheet.getRange('H44:I47').setBorder(true, true, true, true, false, false, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  114.  
  115.   //Agrega logo
  116.   if(sheetName == 'Piédica')
  117.     link = 'https://i.imgur.com/Rhzpjbz.jpg'
  118.   else if (sheetName == 'Sensor Medica')
  119.     link = 'https://i.imgur.com/5kx7CE1.jpg';
  120.   newSheet.insertImage(link, 8, 2);
  121.  
  122.   //Formatea moneda, para mostrar $ antes del monto
  123.   newSheet.getRange('H21:I29').setNumberFormat("$ #,##0.00;$(#,##0.00)");
  124.   newSheet.getRange('I30').setNumberFormat("$ #,##0.00;$(#,##0.00)");
  125.   newSheet.getRange('H33:I41').setNumberFormat("$ #,##0.00;$(#,##0.00)");
  126.   newSheet.getRange('I42').setNumberFormat("$ #,##0.00;$(#,##0.00)");
  127.   newSheet.getRange('I44').setNumberFormat("$ #,##0.00;$(#,##0.00)");
  128.   newSheet.getRange('I46').setNumberFormat("$ #,##0.00;$(#,##0.00)");
  129.   newSheet.getRange('I47').setNumberFormat("$ #,##0.00;$(#,##0.00)");
  130.   newSheet.getRange('J47').setNumberFormat("$ #,##0.00;$(#,##0.00)");
  131.   //Formatea porcentajes
  132.   newSheet.getRange('F49:F51').setNumberFormat("##0.00");
  133.   newSheet.getRange('F49').setValue(newSheet.getRange('F49').getDisplayValue() * 100);
  134.   newSheet.getRange('F50').setValue(newSheet.getRange('F50').getDisplayValue() * 100);
  135.   newSheet.getRange('F51').setValue(newSheet.getRange('F51').getDisplayValue() * 100);
  136.   newSheet.getRange('F52').setValue(newSheet.getRange('F52').getDisplayValue());
  137.  
  138.  
  139.  
  140.   return convertSpreadsheetToPdf('', newFileId, '', fileName);
  141.  
  142. }
  143.  
  144. function moveFiles(sourceFileId, targetFolderId) {
  145.   var file = DriveApp.getFileById(sourceFileId);
  146.   file.getParents().next().removeFile(file);
  147.   DriveApp.getFolderById(targetFolderId).addFile(file);
  148.   return file.getId();
  149. }
  150.  
  151.  
  152.  
  153. function convertSpreadsheetToPdf(email, spreadsheetId, sheetName, pdfName) {
  154.  
  155.   var spreadsheet = spreadsheetId ? SpreadsheetApp.openById(spreadsheetId) : SpreadsheetApp.getActiveSpreadsheet();
  156.   spreadsheetId = spreadsheetId ? spreadsheetId : spreadsheet.getId()  
  157.   var sheetId = sheetName ? spreadsheet.getSheetByName(sheetName).getSheetId() : null;  
  158.   var pdfName = pdfName ? pdfName : spreadsheet.getName();
  159.   var parents = DriveApp.getFileById(spreadsheetId).getParents();
  160.   var folder = parents.hasNext() ? parents.next() : DriveApp.getRootFolder();
  161.   var url_base = spreadsheet.getUrl().replace(/edit$/,'');
  162.  
  163.   var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
  164.  
  165.       // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
  166.       + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))
  167.       // following parameters are optional...
  168.       + '&size=letter'      // paper size
  169.       + '&portrait=true'    // orientation, false for landscape
  170.       + '&fitw=true'        // fit to width, false for actual size
  171.       + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
  172.       + '&gridlines=false'  // hide gridlines
  173.       + '&fzr=false';       // do not repeat row headers (frozen rows) on each page
  174.  
  175.   var options = {
  176.     headers: {
  177.       'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
  178.     }
  179.   }
  180.  
  181.   var response = UrlFetchApp.fetch(url_base + url_ext, options);
  182.   var blob = response.getBlob().setName(pdfName + '.pdf');
  183.   var file = folder.createFile(blob);
  184.  
  185.   if (email) {
  186.  
  187.     var mailOptions = {
  188.       attachments:blob
  189.     }
  190.  
  191.     MailApp.sendEmail(
  192.       email,
  193.       "Here is a file named " + pdfName,
  194.       "Please let me know if you have any questions or comments.",
  195.       mailOptions);
  196.   }
  197.  
  198.   return file.getUrl()
  199.  
  200. }
  201.  
  202. function agregarListaCotizaciones(sheet, sheetName, codigo, idCliente, nombreCliente, fechaEnvio, moneda, cambio, total, fileUrl) {
  203.   var sheet = SpreadsheetApp.getActiveSpreadsheet();
  204.  
  205.   var now = new Date();
  206.   var fecha = Utilities.formatDate(now, "GMT-6", "dd/MM/yyyy HH:mm:ss");
  207.  
  208.    
  209.   var row = [codigo, fecha, idCliente, nombreCliente, fechaEnvio, moneda, cambio, total, fileUrl];
  210.              
  211.   sheet.getSheetByName('Cotizaciones').appendRow(row);
  212. }
RAW Paste Data
Pastebin PRO WINTER Special!
Get 40% OFF Pastebin PRO accounts!
Top