ایجاد & فایل‌های PDF را از Google Sheets ارسال کنید

سطح کدنویسی : مبتدی
مدت زمان : 15 دقیقه
نوع پروژه : اتوماسیون با منوی سفارشی

اهداف

• درک کنید که راه حل چه کاری انجام می دهد.
• آنچه را که سرویس های Apps Script در راه حل انجام می دهند، بدانید.
• اسکریپت را تنظیم کنید
• اسکریپت را اجرا کنید.

در مورد این راه حل

به‌طور خودکار فایل‌های PDF با اطلاعات برگه‌های صفحه‌گسترده Google Sheets ایجاد کنید. پس از تولید فایل‌های PDF، می‌توانید آن‌ها را مستقیماً از Sheets ایمیل کنید. این راه حل بر ایجاد فاکتورهای سفارشی تمرکز دارد، اما می توانید قالب و اسکریپت را متناسب با نیاز خود به روز کنید.

چگونه کار می کند

این اسکریپت از برگه قالب فاکتور به عنوان یک الگو برای تولید PDF استفاده می کند. اطلاعات از برگه های دیگر برای پر کردن سلول های خاص روی الگو تهیه می شود. برای ایمیل کردن فایل‌های PDF، اسکریپت از طریق برگه فاکتورها تکرار می‌شود تا پیوند PDF و آدرس ایمیل مرتبط را دریافت کند. اسکریپت یک موضوع و متن ایمیل عمومی ایجاد می کند و PDF را قبل از ارسال پیوست می کند.

خدمات اسکریپت برنامه ها

این راه حل از خدمات زیر استفاده می کند:

• سرویس صفحه گسترده - تمام اطلاعات را برای تولید PDF فاکتور و ایجاد ایمیل ارائه می دهد. وقتی کاربر روی Reset template در منوی سفارشی کلیک می کند، داده ها را از الگو پاک می کند.
• سرویس Utilities – اسکریپت را با روش sleep() در حین تکرار از طریق هر مشتری متوقف می کند تا اطمینان حاصل شود که اطلاعات صحیح به هر فاکتور اضافه می شود.
• سرویس واکشی URL - برگه قالب فاکتور را به یک PDF صادر می کند.
• سرویس اسکریپت - سرویس واکشی URL را برای دسترسی به صفحه گسترده مجاز می کند.
• سرویس Drive - یک پوشه برای PDF های صادر شده ایجاد می کند. فایل های PDF را به ایمیل ها پیوست می کند.
• سرویس جیمیل - ایمیل ها را می سازد و ارسال می کند.

پیش نیازها

برای استفاده از این نمونه به پیش نیازهای زیر نیاز دارید:

• یک حساب Google (حساب‌های Google Workspace ممکن است به تأیید سرپرست نیاز داشته باشند).
• یک مرورگر وب با دسترسی به اینترنت.

اسکریپت را تنظیم کنید

1. برای کپی کردن ایجاد و ارسال PDF از صفحه گسترده Google Sheets، روی دکمه زیر کلیک کنید. پروژه Apps Script برای این راه حل به صفحه گسترده پیوست شده است.
   یک کپی تهیه کنید

2. روی برنامه‌های افزودنی > اسکریپت برنامه‌ها کلیک کنید.

3. در فایل Code.gs ، متغیرهای زیر را به روز کنید:

   1. EMAIL_OVERRIDE را روی true تنظیم کنید.
   2. EMAIL_ADDRESS_OVERRIDE به آدرس ایمیل خود تنظیم کنید.

4. روی ذخیره کلیک کنید .

اسکریپت را اجرا کنید

1. به صفحه‌گسترده بازگردید و روی ایجاد و ارسال فایل‌های PDF > پردازش فاکتورها کلیک کنید.

2. وقتی از شما خواسته شد، اسکریپت را مجاز کنید. اگر صفحه رضایت OAuth اخطار را نشان می‌دهد، این برنامه تأیید نشده است ، با انتخاب پیشرفته > رفتن به {Project Name} (ناامن) ادامه دهید.

3. روی ایجاد و ارسال فایل‌های PDF > پردازش فاکتورها کلیک کنید.

4. برای مشاهده فایل‌های PDF، به برگه فاکتورها بروید و روی پیوندهای ستون پیوند فاکتور کلیک کنید.

5. روی Generate and send PDFs > Send emails کلیک کنید.

6. برای بررسی ایمیل ها و فایل های PDF پیوست شده، ایمیل خود را بررسی کنید. از آنجایی که در بخش قبلی EMAIL_OVERRIDE روی true تنظیم کرده‌اید، اسکریپت همه ایمیل‌ها را به آدرس ایمیلی که برای EMAIL_ADDRESS_OVERRIDE مشخص کرده‌اید ارسال می‌کند. اگر EMAIL_OVERRIDE را روی false تنظیم کنید، اسکریپت ایمیل ها را به آدرس های ایمیل فهرست شده در برگه مشتریان ارسال می کند.

7. (اختیاری) برای پاک کردن داده‌ها از برگه الگوی فاکتور ، روی ایجاد و ارسال فایل‌های PDF > بازنشانی الگو کلیک کنید.

کد را مرور کنید

برای بررسی کد Apps Script برای این راه حل، روی مشاهده کد منبع در زیر کلیک کنید:

مشاهده کد منبع

Code.gs

solutions/automations/generate-pdfs/Code.js

در GitHub مشاهده کنید

// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/generate-pdfs

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

// TODO: To test this solution, set EMAIL_OVERRIDE to true and set EMAIL_ADDRESS_OVERRIDE to your email address.
const EMAIL_OVERRIDE = false;
const EMAIL_ADDRESS_OVERRIDE = '[e-mail]';

// Application constants
const APP_TITLE = 'Generate and send PDFs';
const OUTPUT_FOLDER_NAME = "Customer PDFs";
const DUE_DATE_NUM_DAYS = 15

// Sheet name constants. Update if you change the names of the sheets.
const CUSTOMERS_SHEET_NAME = 'Customers';
const PRODUCTS_SHEET_NAME = 'Products';
const TRANSACTIONS_SHEET_NAME = 'Transactions';
const INVOICES_SHEET_NAME = 'Invoices';
const INVOICE_TEMPLATE_SHEET_NAME = 'Invoice Template';

// Email constants
const EMAIL_SUBJECT = 'Invoice Notification';
const EMAIL_BODY = 'Hello!\rPlease see the attached PDF document.';


/**
 * Iterates through the worksheet data populating the template sheet with 
 * customer data, then saves each instance as a PDF document.
 * 
 * Called by user via custom menu item.
 */
function processDocuments() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const customersSheet = ss.getSheetByName(CUSTOMERS_SHEET_NAME);
  const productsSheet = ss.getSheetByName(PRODUCTS_SHEET_NAME);
  const transactionsSheet = ss.getSheetByName(TRANSACTIONS_SHEET_NAME);
  const invoicesSheet = ss.getSheetByName(INVOICES_SHEET_NAME);
  const invoiceTemplateSheet = ss.getSheetByName(INVOICE_TEMPLATE_SHEET_NAME);

  // Gets data from the storage sheets as objects.
  const customers = dataRangeToObject(customersSheet);
  const products = dataRangeToObject(productsSheet);
  const transactions = dataRangeToObject(transactionsSheet);

  ss.toast('Creating Invoices', APP_TITLE, 1);
  const invoices = [];

  // Iterates for each customer calling createInvoiceForCustomer routine.
  customers.forEach(function (customer) {
    ss.toast(`Creating Invoice for ${customer.customer_name}`, APP_TITLE, 1);
    let invoice = createInvoiceForCustomer(
      customer, products, transactions, invoiceTemplateSheet, ss.getId());
    invoices.push(invoice);
  });
  // Writes invoices data to the sheet.
  invoicesSheet.getRange(2, 1, invoices.length, invoices[0].length).setValues(invoices);
}

/**
 * Processes each customer instance with passed in data parameters.
 * 
 * @param {object} customer - Object for the customer
 * @param {object} products - Object for all the products
 * @param {object} transactions - Object for all the transactions
 * @param {object} invoiceTemplateSheet - Object for the invoice template sheet
 * @param {string} ssId - Google Sheet ID     
 * Return {array} of instance customer invoice data
 */
function createInvoiceForCustomer(customer, products, transactions, templateSheet, ssId) {
  let customerTransactions = transactions.filter(function (transaction) {
    return transaction.customer_name == customer.customer_name;
  });

  // Clears existing data from the template.
  clearTemplateSheet();

  let lineItems = [];
  let totalAmount = 0;
  customerTransactions.forEach(function (lineItem) {
    let lineItemProduct = products.filter(function (product) {
      return product.sku_name == lineItem.sku;
    })[0];
    const qty = parseInt(lineItem.licenses);
    const price = parseFloat(lineItemProduct.price).toFixed(2);
    const amount = parseFloat(qty * price).toFixed(2);
    lineItems.push([lineItemProduct.sku_name, lineItemProduct.sku_description, '', qty, price, amount]);
    totalAmount += parseFloat(amount);
  });

  // Generates a random invoice number. You can replace with your own document ID method.
  const invoiceNumber = Math.floor(100000 + Math.random() * 900000);

  // Calulates dates.
  const todaysDate = new Date().toDateString()
  const dueDate = new Date(Date.now() + 1000 * 60 * 60 * 24 * DUE_DATE_NUM_DAYS).toDateString()

  // Sets values in the template.
  templateSheet.getRange('B10').setValue(customer.customer_name)
  templateSheet.getRange('B11').setValue(customer.address)
  templateSheet.getRange('F10').setValue(invoiceNumber)
  templateSheet.getRange('F12').setValue(todaysDate)
  templateSheet.getRange('F14').setValue(dueDate)
  templateSheet.getRange(18, 2, lineItems.length, 6).setValues(lineItems);

  // Cleans up and creates PDF.
  SpreadsheetApp.flush();
  Utilities.sleep(500); // Using to offset any potential latency in creating .pdf
  const pdf = createPDF(ssId, templateSheet, `Invoice#${invoiceNumber}-${customer.customer_name}`);
  return [invoiceNumber, todaysDate, customer.customer_name, customer.email, '', totalAmount, dueDate, pdf.getUrl(), 'No'];
}

/**
* Resets the template sheet by clearing out customer data.
* You use this to prepare for the next iteration or to view blank
* the template for design.
* 
* Called by createInvoiceForCustomer() or by the user via custom menu item.
*/
function clearTemplateSheet() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const templateSheet = ss.getSheetByName(INVOICE_TEMPLATE_SHEET_NAME);
  // Clears existing data from the template.
  const rngClear = templateSheet.getRangeList(['B10:B11', 'F10', 'F12', 'F14']).getRanges()
  rngClear.forEach(function (cell) {
    cell.clearContent();
  });
  // This sample only accounts for six rows of data 'B18:G24'. You can extend or make dynamic as necessary.
  templateSheet.getRange(18, 2, 7, 6).clearContent();
}

/**
 * Creates a PDF for the customer given sheet.
 * @param {string} ssId - Id of the Google Spreadsheet
 * @param {object} sheet - Sheet to be converted as PDF
 * @param {string} pdfName - File name of the PDF being created
 * @return {file object} PDF file as a blob
 */
function createPDF(ssId, sheet, pdfName) {
  const fr = 0, fc = 0, lc = 9, lr = 27;
  const url = "https://docs.google.com/spreadsheets/d/" + ssId + "/export" +
    "?format=pdf&" +
    "size=7&" +
    "fzr=true&" +
    "portrait=true&" +
    "fitw=true&" +
    "gridlines=false&" +
    "printtitle=false&" +
    "top_margin=0.5&" +
    "bottom_margin=0.25&" +
    "left_margin=0.5&" +
    "right_margin=0.5&" +
    "sheetnames=false&" +
    "pagenum=UNDEFINED&" +
    "attachment=true&" +
    "gid=" + sheet.getSheetId() + '&' +
    "r1=" + fr + "&c1=" + fc + "&r2=" + lr + "&c2=" + lc;

  const params = { method: "GET", headers: { "authorization": "Bearer " + ScriptApp.getOAuthToken() } };
  const blob = UrlFetchApp.fetch(url, params).getBlob().setName(pdfName + '.pdf');

  // Gets the folder in Drive where the PDFs are stored.
  const folder = getFolderByName_(OUTPUT_FOLDER_NAME);

  const pdfFile = folder.createFile(blob);
  return pdfFile;
}


/**
 * Sends emails with PDF as an attachment.
 * Checks/Sets 'Email Sent' column to 'Yes' to avoid resending.
 * 
 * Called by user via custom menu item.
 */
function sendEmails() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const invoicesSheet = ss.getSheetByName(INVOICES_SHEET_NAME);
  const invoicesData = invoicesSheet.getRange(1, 1, invoicesSheet.getLastRow(), invoicesSheet.getLastColumn()).getValues();
  const keysI = invoicesData.splice(0, 1)[0];
  const invoices = getObjects(invoicesData, createObjectKeys(keysI));
  ss.toast('Emailing Invoices', APP_TITLE, 1);
  invoices.forEach(function (invoice, index) {

    if (invoice.email_sent != 'Yes') {
      ss.toast(`Emailing Invoice for ${invoice.customer}`, APP_TITLE, 1);

      const fileId = invoice.invoice_link.match(/[-\w]{25,}(?!.*[-\w]{25,})/)
      const attachment = DriveApp.getFileById(fileId);

      let recipient = invoice.email;
      if (EMAIL_OVERRIDE) {
        recipient = EMAIL_ADDRESS_OVERRIDE
      }

      GmailApp.sendEmail(recipient, EMAIL_SUBJECT, EMAIL_BODY, {
        attachments: [attachment.getAs(MimeType.PDF)],
        name: APP_TITLE
      });
      invoicesSheet.getRange(index + 2, 9).setValue('Yes');
    }
  });
}

/**
 * Helper function that turns sheet data range into an object. 
 * 
 * @param {SpreadsheetApp.Sheet} sheet - Sheet to process
 * Return {object} of a sheet's datarange as an object 
 */
function dataRangeToObject(sheet) {
  const dataRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const keys = dataRange.splice(0, 1)[0];
  return getObjects(dataRange, createObjectKeys(keys));
}

/**
 * Utility function for mapping sheet data to objects.
 */
function getObjects(data, keys) {
  let objects = [];
  for (let i = 0; i < data.length; ++i) {
    let object = {};
    let hasData = false;
    for (let j = 0; j < data[i].length; ++j) {
      let cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}
// Creates object keys for column headers.
function createObjectKeys(keys) {
  return keys.map(function (key) {
    return key.replace(/\W+/g, '_').toLowerCase();
  });
}
// Returns true if the cell where cellData was read from is empty.
function isCellEmpty(cellData) {
  return typeof (cellData) == "string" && cellData == "";
}

منو.gs

solutions/automations/generate-pdfs/Menu.js

در GitHub مشاهده کنید

/**
 * Copyright 2022 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

/**
 * @OnlyCurrentDoc
 *
 * The above comment specifies that this automation will only
 * attempt to read or modify the spreadsheet this script is bound to.
 * The authorization request message presented to users reflects the
 * limited scope.
 */

/**
 * Creates a custom menu in the Google Sheets UI when the document is opened.
 *
 * @param {object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {

const menu = SpreadsheetApp.getUi().createMenu(APP_TITLE)
  menu
    .addItem('Process invoices', 'processDocuments')
    .addItem('Send emails', 'sendEmails')
    .addSeparator()
    .addItem('Reset template', 'clearTemplateSheet')
    .addToUi();
}

Utilities.gs

solutions/automations/generate-pdfs/Utilities.js

در GitHub مشاهده کنید

/**
 * Copyright 2022 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

/**
 * Returns a Google Drive folder in the same location 
 * in Drive where the spreadsheet is located. First, it checks if the folder
 * already exists and returns that folder. If the folder doesn't already
 * exist, the script creates a new one. The folder's name is set by the
 * "OUTPUT_FOLDER_NAME" variable from the Code.gs file.
 *
 * @param {string} folderName - Name of the Drive folder. 
 * @return {object} Google Drive Folder
 */
function getFolderByName_(folderName) {

  // Gets the Drive Folder of where the current spreadsheet is located.
  const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const parentFolder = DriveApp.getFileById(ssId).getParents().next();

  // Iterates the subfolders to check if the PDF folder already exists.
  const subFolders = parentFolder.getFolders();
  while (subFolders.hasNext()) {
    let folder = subFolders.next();

    // Returns the existing folder if found.
    if (folder.getName() === folderName) {
      return folder;
    }
  }
  // Creates a new folder if one does not already exist.
  return parentFolder.createFolder(folderName)
    .setDescription(`Created by ${APP_TITLE} application to store PDF output files`);
}

/**
 * Test function to run getFolderByName_.
 * @prints a Google Drive FolderId.
 */
function test_getFolderByName() {

  // Gets the PDF folder in Drive.
  const folder = getFolderByName_(OUTPUT_FOLDER_NAME);

  console.log(`Name: ${folder.getName()}\rID: ${folder.getId()}\rDescription: ${folder.getDescription()}`)
  // To automatically delete test folder, uncomment the following code:
  // folder.setTrashed(true);
}

مشارکت کنندگان

این نمونه توسط گوگل با کمک کارشناسان توسعه دهنده گوگل نگهداری می شود.

مراحل بعدی

• منوهای سفارشی در Google Workspace
• Google Sheets را گسترش دهید

،

سطح کدنویسی : مبتدی
مدت زمان : 15 دقیقه
نوع پروژه : اتوماسیون با منوی سفارشی

اهداف

• درک کنید که راه حل چه کاری انجام می دهد.
• آنچه را که سرویس های Apps Script در راه حل انجام می دهند، بدانید.
• اسکریپت را تنظیم کنید
• اسکریپت را اجرا کنید.

در مورد این راه حل

به‌طور خودکار فایل‌های PDF با اطلاعات برگه‌های صفحه‌گسترده Google Sheets ایجاد کنید. پس از تولید فایل‌های PDF، می‌توانید آن‌ها را مستقیماً از Sheets ایمیل کنید. این راه حل بر ایجاد فاکتورهای سفارشی تمرکز دارد، اما می توانید قالب و اسکریپت را متناسب با نیاز خود به روز کنید.

چگونه کار می کند

این اسکریپت از برگه قالب فاکتور به عنوان یک الگو برای تولید PDF استفاده می کند. اطلاعات از برگه های دیگر برای پر کردن سلول های خاص روی الگو تهیه می شود. برای ایمیل کردن فایل‌های PDF، اسکریپت از طریق برگه فاکتورها تکرار می‌شود تا پیوند PDF و آدرس ایمیل مرتبط را دریافت کند. اسکریپت یک موضوع و متن ایمیل عمومی ایجاد می کند و PDF را قبل از ارسال پیوست می کند.

خدمات اسکریپت برنامه ها

این راه حل از خدمات زیر استفاده می کند:

• سرویس صفحه گسترده - تمام اطلاعات را برای تولید PDF فاکتور و ایجاد ایمیل ارائه می دهد. وقتی کاربر روی Reset template در منوی سفارشی کلیک می کند، داده ها را از الگو پاک می کند.
• سرویس Utilities – اسکریپت را با روش sleep() در حین تکرار از طریق هر مشتری متوقف می کند تا اطمینان حاصل شود که اطلاعات صحیح به هر فاکتور اضافه می شود.
• سرویس واکشی URL - برگه قالب فاکتور را به یک PDF صادر می کند.
• سرویس اسکریپت - سرویس واکشی URL را برای دسترسی به صفحه گسترده مجاز می کند.
• سرویس Drive - یک پوشه برای PDF های صادر شده ایجاد می کند. فایل های PDF را به ایمیل ها پیوست می کند.
• سرویس جیمیل - ایمیل ها را می سازد و ارسال می کند.

پیش نیازها

برای استفاده از این نمونه به پیش نیازهای زیر نیاز دارید:

• یک حساب Google (حساب‌های Google Workspace ممکن است به تأیید سرپرست نیاز داشته باشند).
• یک مرورگر وب با دسترسی به اینترنت.

اسکریپت را تنظیم کنید

1. برای کپی کردن ایجاد و ارسال PDF از صفحه گسترده Google Sheets، روی دکمه زیر کلیک کنید. پروژه Apps Script برای این راه حل به صفحه گسترده پیوست شده است.
   یک کپی تهیه کنید

2. روی برنامه‌های افزودنی > اسکریپت برنامه‌ها کلیک کنید.

3. در فایل Code.gs ، متغیرهای زیر را به روز کنید:

   1. EMAIL_OVERRIDE را روی true تنظیم کنید.
   2. EMAIL_ADDRESS_OVERRIDE به آدرس ایمیل خود تنظیم کنید.

4. روی ذخیره کلیک کنید .

اسکریپت را اجرا کنید

1. به صفحه‌گسترده بازگردید و روی ایجاد و ارسال فایل‌های PDF > پردازش فاکتورها کلیک کنید.

2. وقتی از شما خواسته شد، اسکریپت را مجاز کنید. اگر صفحه رضایت OAuth اخطار را نشان می‌دهد، این برنامه تأیید نشده است ، با انتخاب پیشرفته > رفتن به {Project Name} (ناامن) ادامه دهید.

3. روی ایجاد و ارسال فایل‌های PDF > پردازش فاکتورها کلیک کنید.

4. برای مشاهده فایل‌های PDF، به برگه فاکتورها بروید و روی پیوندهای ستون پیوند فاکتور کلیک کنید.

5. روی Generate and send PDFs > Send emails کلیک کنید.

6. برای بررسی ایمیل ها و فایل های PDF پیوست شده، ایمیل خود را بررسی کنید. از آنجایی که در بخش قبلی EMAIL_OVERRIDE روی true تنظیم کرده‌اید، اسکریپت همه ایمیل‌ها را به آدرس ایمیلی که برای EMAIL_ADDRESS_OVERRIDE مشخص کرده‌اید ارسال می‌کند. اگر EMAIL_OVERRIDE را روی false تنظیم کنید، اسکریپت ایمیل ها را به آدرس های ایمیل فهرست شده در برگه مشتریان ارسال می کند.

7. (اختیاری) برای پاک کردن داده‌ها از برگه الگوی فاکتور ، روی ایجاد و ارسال فایل‌های PDF > بازنشانی الگو کلیک کنید.

کد را مرور کنید

برای بررسی کد Apps Script برای این راه حل، روی مشاهده کد منبع در زیر کلیک کنید:

مشاهده کد منبع

Code.gs

solutions/automations/generate-pdfs/Code.js

در GitHub مشاهده کنید

// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/generate-pdfs

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

// TODO: To test this solution, set EMAIL_OVERRIDE to true and set EMAIL_ADDRESS_OVERRIDE to your email address.
const EMAIL_OVERRIDE = false;
const EMAIL_ADDRESS_OVERRIDE = '[e-mail]';

// Application constants
const APP_TITLE = 'Generate and send PDFs';
const OUTPUT_FOLDER_NAME = "Customer PDFs";
const DUE_DATE_NUM_DAYS = 15

// Sheet name constants. Update if you change the names of the sheets.
const CUSTOMERS_SHEET_NAME = 'Customers';
const PRODUCTS_SHEET_NAME = 'Products';
const TRANSACTIONS_SHEET_NAME = 'Transactions';
const INVOICES_SHEET_NAME = 'Invoices';
const INVOICE_TEMPLATE_SHEET_NAME = 'Invoice Template';

// Email constants
const EMAIL_SUBJECT = 'Invoice Notification';
const EMAIL_BODY = 'Hello!\rPlease see the attached PDF document.';


/**
 * Iterates through the worksheet data populating the template sheet with 
 * customer data, then saves each instance as a PDF document.
 * 
 * Called by user via custom menu item.
 */
function processDocuments() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const customersSheet = ss.getSheetByName(CUSTOMERS_SHEET_NAME);
  const productsSheet = ss.getSheetByName(PRODUCTS_SHEET_NAME);
  const transactionsSheet = ss.getSheetByName(TRANSACTIONS_SHEET_NAME);
  const invoicesSheet = ss.getSheetByName(INVOICES_SHEET_NAME);
  const invoiceTemplateSheet = ss.getSheetByName(INVOICE_TEMPLATE_SHEET_NAME);

  // Gets data from the storage sheets as objects.
  const customers = dataRangeToObject(customersSheet);
  const products = dataRangeToObject(productsSheet);
  const transactions = dataRangeToObject(transactionsSheet);

  ss.toast('Creating Invoices', APP_TITLE, 1);
  const invoices = [];

  // Iterates for each customer calling createInvoiceForCustomer routine.
  customers.forEach(function (customer) {
    ss.toast(`Creating Invoice for ${customer.customer_name}`, APP_TITLE, 1);
    let invoice = createInvoiceForCustomer(
      customer, products, transactions, invoiceTemplateSheet, ss.getId());
    invoices.push(invoice);
  });
  // Writes invoices data to the sheet.
  invoicesSheet.getRange(2, 1, invoices.length, invoices[0].length).setValues(invoices);
}

/**
 * Processes each customer instance with passed in data parameters.
 * 
 * @param {object} customer - Object for the customer
 * @param {object} products - Object for all the products
 * @param {object} transactions - Object for all the transactions
 * @param {object} invoiceTemplateSheet - Object for the invoice template sheet
 * @param {string} ssId - Google Sheet ID     
 * Return {array} of instance customer invoice data
 */
function createInvoiceForCustomer(customer, products, transactions, templateSheet, ssId) {
  let customerTransactions = transactions.filter(function (transaction) {
    return transaction.customer_name == customer.customer_name;
  });

  // Clears existing data from the template.
  clearTemplateSheet();

  let lineItems = [];
  let totalAmount = 0;
  customerTransactions.forEach(function (lineItem) {
    let lineItemProduct = products.filter(function (product) {
      return product.sku_name == lineItem.sku;
    })[0];
    const qty = parseInt(lineItem.licenses);
    const price = parseFloat(lineItemProduct.price).toFixed(2);
    const amount = parseFloat(qty * price).toFixed(2);
    lineItems.push([lineItemProduct.sku_name, lineItemProduct.sku_description, '', qty, price, amount]);
    totalAmount += parseFloat(amount);
  });

  // Generates a random invoice number. You can replace with your own document ID method.
  const invoiceNumber = Math.floor(100000 + Math.random() * 900000);

  // Calulates dates.
  const todaysDate = new Date().toDateString()
  const dueDate = new Date(Date.now() + 1000 * 60 * 60 * 24 * DUE_DATE_NUM_DAYS).toDateString()

  // Sets values in the template.
  templateSheet.getRange('B10').setValue(customer.customer_name)
  templateSheet.getRange('B11').setValue(customer.address)
  templateSheet.getRange('F10').setValue(invoiceNumber)
  templateSheet.getRange('F12').setValue(todaysDate)
  templateSheet.getRange('F14').setValue(dueDate)
  templateSheet.getRange(18, 2, lineItems.length, 6).setValues(lineItems);

  // Cleans up and creates PDF.
  SpreadsheetApp.flush();
  Utilities.sleep(500); // Using to offset any potential latency in creating .pdf
  const pdf = createPDF(ssId, templateSheet, `Invoice#${invoiceNumber}-${customer.customer_name}`);
  return [invoiceNumber, todaysDate, customer.customer_name, customer.email, '', totalAmount, dueDate, pdf.getUrl(), 'No'];
}

/**
* Resets the template sheet by clearing out customer data.
* You use this to prepare for the next iteration or to view blank
* the template for design.
* 
* Called by createInvoiceForCustomer() or by the user via custom menu item.
*/
function clearTemplateSheet() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const templateSheet = ss.getSheetByName(INVOICE_TEMPLATE_SHEET_NAME);
  // Clears existing data from the template.
  const rngClear = templateSheet.getRangeList(['B10:B11', 'F10', 'F12', 'F14']).getRanges()
  rngClear.forEach(function (cell) {
    cell.clearContent();
  });
  // This sample only accounts for six rows of data 'B18:G24'. You can extend or make dynamic as necessary.
  templateSheet.getRange(18, 2, 7, 6).clearContent();
}

/**
 * Creates a PDF for the customer given sheet.
 * @param {string} ssId - Id of the Google Spreadsheet
 * @param {object} sheet - Sheet to be converted as PDF
 * @param {string} pdfName - File name of the PDF being created
 * @return {file object} PDF file as a blob
 */
function createPDF(ssId, sheet, pdfName) {
  const fr = 0, fc = 0, lc = 9, lr = 27;
  const url = "https://docs.google.com/spreadsheets/d/" + ssId + "/export" +
    "?format=pdf&" +
    "size=7&" +
    "fzr=true&" +
    "portrait=true&" +
    "fitw=true&" +
    "gridlines=false&" +
    "printtitle=false&" +
    "top_margin=0.5&" +
    "bottom_margin=0.25&" +
    "left_margin=0.5&" +
    "right_margin=0.5&" +
    "sheetnames=false&" +
    "pagenum=UNDEFINED&" +
    "attachment=true&" +
    "gid=" + sheet.getSheetId() + '&' +
    "r1=" + fr + "&c1=" + fc + "&r2=" + lr + "&c2=" + lc;

  const params = { method: "GET", headers: { "authorization": "Bearer " + ScriptApp.getOAuthToken() } };
  const blob = UrlFetchApp.fetch(url, params).getBlob().setName(pdfName + '.pdf');

  // Gets the folder in Drive where the PDFs are stored.
  const folder = getFolderByName_(OUTPUT_FOLDER_NAME);

  const pdfFile = folder.createFile(blob);
  return pdfFile;
}


/**
 * Sends emails with PDF as an attachment.
 * Checks/Sets 'Email Sent' column to 'Yes' to avoid resending.
 * 
 * Called by user via custom menu item.
 */
function sendEmails() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const invoicesSheet = ss.getSheetByName(INVOICES_SHEET_NAME);
  const invoicesData = invoicesSheet.getRange(1, 1, invoicesSheet.getLastRow(), invoicesSheet.getLastColumn()).getValues();
  const keysI = invoicesData.splice(0, 1)[0];
  const invoices = getObjects(invoicesData, createObjectKeys(keysI));
  ss.toast('Emailing Invoices', APP_TITLE, 1);
  invoices.forEach(function (invoice, index) {

    if (invoice.email_sent != 'Yes') {
      ss.toast(`Emailing Invoice for ${invoice.customer}`, APP_TITLE, 1);

      const fileId = invoice.invoice_link.match(/[-\w]{25,}(?!.*[-\w]{25,})/)
      const attachment = DriveApp.getFileById(fileId);

      let recipient = invoice.email;
      if (EMAIL_OVERRIDE) {
        recipient = EMAIL_ADDRESS_OVERRIDE
      }

      GmailApp.sendEmail(recipient, EMAIL_SUBJECT, EMAIL_BODY, {
        attachments: [attachment.getAs(MimeType.PDF)],
        name: APP_TITLE
      });
      invoicesSheet.getRange(index + 2, 9).setValue('Yes');
    }
  });
}

/**
 * Helper function that turns sheet data range into an object. 
 * 
 * @param {SpreadsheetApp.Sheet} sheet - Sheet to process
 * Return {object} of a sheet's datarange as an object 
 */
function dataRangeToObject(sheet) {
  const dataRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const keys = dataRange.splice(0, 1)[0];
  return getObjects(dataRange, createObjectKeys(keys));
}

/**
 * Utility function for mapping sheet data to objects.
 */
function getObjects(data, keys) {
  let objects = [];
  for (let i = 0; i < data.length; ++i) {
    let object = {};
    let hasData = false;
    for (let j = 0; j < data[i].length; ++j) {
      let cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}
// Creates object keys for column headers.
function createObjectKeys(keys) {
  return keys.map(function (key) {
    return key.replace(/\W+/g, '_').toLowerCase();
  });
}
// Returns true if the cell where cellData was read from is empty.
function isCellEmpty(cellData) {
  return typeof (cellData) == "string" && cellData == "";
}

منو.gs

solutions/automations/generate-pdfs/Menu.js

در GitHub مشاهده کنید

/**
 * Copyright 2022 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

/**
 * @OnlyCurrentDoc
 *
 * The above comment specifies that this automation will only
 * attempt to read or modify the spreadsheet this script is bound to.
 * The authorization request message presented to users reflects the
 * limited scope.
 */

/**
 * Creates a custom menu in the Google Sheets UI when the document is opened.
 *
 * @param {object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {

const menu = SpreadsheetApp.getUi().createMenu(APP_TITLE)
  menu
    .addItem('Process invoices', 'processDocuments')
    .addItem('Send emails', 'sendEmails')
    .addSeparator()
    .addItem('Reset template', 'clearTemplateSheet')
    .addToUi();
}

Utilities.gs

solutions/automations/generate-pdfs/Utilities.js

در GitHub مشاهده کنید

/**
 * Copyright 2022 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

/**
 * Returns a Google Drive folder in the same location 
 * in Drive where the spreadsheet is located. First, it checks if the folder
 * already exists and returns that folder. If the folder doesn't already
 * exist, the script creates a new one. The folder's name is set by the
 * "OUTPUT_FOLDER_NAME" variable from the Code.gs file.
 *
 * @param {string} folderName - Name of the Drive folder. 
 * @return {object} Google Drive Folder
 */
function getFolderByName_(folderName) {

  // Gets the Drive Folder of where the current spreadsheet is located.
  const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const parentFolder = DriveApp.getFileById(ssId).getParents().next();

  // Iterates the subfolders to check if the PDF folder already exists.
  const subFolders = parentFolder.getFolders();
  while (subFolders.hasNext()) {
    let folder = subFolders.next();

    // Returns the existing folder if found.
    if (folder.getName() === folderName) {
      return folder;
    }
  }
  // Creates a new folder if one does not already exist.
  return parentFolder.createFolder(folderName)
    .setDescription(`Created by ${APP_TITLE} application to store PDF output files`);
}

/**
 * Test function to run getFolderByName_.
 * @prints a Google Drive FolderId.
 */
function test_getFolderByName() {

  // Gets the PDF folder in Drive.
  const folder = getFolderByName_(OUTPUT_FOLDER_NAME);

  console.log(`Name: ${folder.getName()}\rID: ${folder.getId()}\rDescription: ${folder.getDescription()}`)
  // To automatically delete test folder, uncomment the following code:
  // folder.setTrashed(true);
}

مشارکت کنندگان

این نمونه توسط گوگل با کمک کارشناسان توسعه دهنده گوگل نگهداری می شود.

مراحل بعدی

• منوهای سفارشی در Google Workspace
• Google Sheets را گسترش دهید