appscript.json

{
  "timeZone": "Asia/Kolkata",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Drive",
        "version": "v2",
        "serviceId": "drive"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "MYSELF"
  }
}

invoice.gs

// enable drive api service
// manually set a time-driven trigger that runs monthly after 25th evening,

const botToken = ""
const chatId = ""
const invoiceFolderId = "" // drive folder id where the invoice files live
const templateFileId = "" // drive file id 
const invoiceReceiverEmail = "";
const invoiceSenderName = "";


function entrypoint() {
  return main();
}

function deIndent(str) {
  return str.replace(/^[ \t]+/gm, "");
}

function dateWithOrdinalSuffix(date) {
  const day = date.getDate();
  let suffix = "th";
  if (day % 10 === 1 && day !== 11) {
    suffix = "st";
  } else if (day % 10 === 2 && day !== 12) {
    suffix = "nd";
  } else if (day % 10 === 3 && day !== 13) {
    suffix = "rd";
  }

  return day + suffix + Utilities.formatDate(date, "Asia/Kolkata", " MMMM, yyyy");
}

function getConsultancyFeesText(date) {
  const firstDay = new Date(date.getFullYear(), date.getMonth(), 1)
  const lastDay = new Date(date.getFullYear(), date.getMonth() + 1, 0)
  return `Consultancy fees for ${dateWithOrdinalSuffix(firstDay)} to ${dateWithOrdinalSuffix(lastDay)}`
}

function tgMessage(message) {
  if (!botToken || !chatId) {
    console.log("skipping telegram notification: not configured")
  }
  UrlFetchApp.fetch(
    `https://api.telegram.org/bot${botToken}/sendMessage`,
    {
      method: 'post',
      payload: {
        chat_id: chatId,
        text: message
      }
    }
  );
}

function fetchWithAuth(url) {
  return UrlFetchApp.fetch(url, {
    method: "GET",
    headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
    muteHttpExceptions: false
  })
}

function main() {
  const date = new Date();
  date.setDate(27)
  const monthYear = Utilities.formatDate(date, "Asia/Kolkata", "MMMM yyyy");
  const data = {
    "H4": Utilities.formatDate(date, "Asia/Kolkata", "dd/MM/yyyy"), // date
    "A12": getConsultancyFeesText(date),
  }

  console.log("processing invoice for ", monthYear)
  try {
    const invoiceFolder = DriveApp.getFolderById(invoiceFolderId);

    // Get invoice thread for current month
    const threads = GmailApp.search(`from:${invoiceReceiverEmail} subject:Invoice | Consultation Charges | ${monthYear}`);
    if (threads.length === 0) {
      console.log(`🔥 Invoice processing for ${monthYear} failed: invoice email not found`)
      tgMessage(`🔥 Invoice processing for ${monthYear} failed: invoice email not found`)
      return
    };
    const thread = threads[0];
    if (thread.getMessages().length > 2){
      console.log(`⚠️ Invoice processing for ${monthYear} skipped: draft already present`)
      tgMessage(`⚠️ Invoice processing for ${monthYear} skipped: draft already present`)
      return
    }

    // Make a temp copy of the template file 
    const templateFile = DriveApp.getFileById(templateFileId).makeCopy();
    console.log("temp template file created: ", templateFile.getId())

    // Open and Edit Google Sheet
    const sheet = SpreadsheetApp.openById(templateFile.getId()).getActiveSheet();

    Object.entries(data).forEach(([key, value]) => {
      sheet.getRange(key).setValue(value);
    });

    // Fix ac getting cutoff
    sheet.getRange("D23").setNumberFormat("@");

    SpreadsheetApp.flush();
    console.log("template file updated");

    // export as PDF
    const templateFileRes = fetchWithAuth(`https://docs.google.com/spreadsheets/d/${templateFile.getId()}/export?format=pdf`)
    const finalFile = invoiceFolder.createFile(templateFileRes.getBlob().setName(`${invoiceSenderName} ${monthYear} invoice.pdf`));
    console.log("final invoice file created: ", finalFile.getId())

    // Delete the temporary Google Sheet
    templateFile.setTrashed(true)

    const message_body = deIndent(
      `Hi,

      Please find my invoice for the month of ${monthYear} attached.

      Thanks & Regards,
      ${invoiceSenderName}`
    )

    // Draft Reply
    thread.createDraftReply(
      message_body,
      { attachments: [finalFile.getBlob()] }
    )
    console.log("draft created");

    tgMessage(`🚀 Invoice processed for ${monthYear} successfully: ${finalFile.getUrl()}`)
  } catch (err) {
    tgMessage(`🔥 Invoice processing for ${monthYear} failed: ${err}`)
    console.error(err)
    throw err
  }
}