Untitled

mail@pastecode.io avatar
unknown
javascript
7 months ago
6.7 kB
1
Indexable
Never


function addTask() {


  function getFormattedDate(d) {
    const date = new Date(d)
    let year = date.getFullYear();
    let month = (1 + date.getMonth()).toString().padStart(2, '0');
    let day = date.getDate().toString().padStart(2, '0');

    // return month + '/' + day + '/' + year;
    return date.toISOString()
  }
  const My_LIST_NAME = "SETTLEMENT"
  var existingList = null;
  try {
    const existingLists = Tasks.Tasklists.list();
    if (existingLists.items.length > 0) {
      existingLists.items.forEach((v) => {
        if (v.title === My_LIST_NAME) {
          existingList = v;
        }
      })
    }
    console.log("existingList : ", existingList)
    if (!existingList) {
      // Create a new task list named "SETTLEMENTS"
      var newList = {
        title: "SETTLEMENTS"
      };
      var createdList = new Tasks.Tasklists.insert({
        name: My_LIST_NAME,
        title: My_LIST_NAME
      });

      console.log("new createdList : ", createdList)

      existingList = createdList;
    }
  } catch (e) {
    console.log("e : ", e)
    // Handle the error if the task list doesn't exist
    if (e.message.includes("Invalid task list ID")) {
      console.log("Task list \"SETTLEMENTS\" does not exist. Creating a new one.");
      // Create a new task list named "SETTLEMENTS"
      var newList = {
        title: "SETTLEMENTS"
      };
      var createdList = new Tasks.Tasklists.insert();

      console.log("createdList : ", createdList)

      existingList = createdList;
    } else {
      throw e; // Rethrow other errors
    }
  }

  // Proceed with the rest of the script using the existingList object
  var listId = existingList.id;
  // Get the date from cell D4
  var date = SpreadsheetApp.getActiveSpreadsheet().getRange("D4").getValue();
  var title = SpreadsheetApp.getActiveSpreadsheet().getRange("E6").getValue();
  const task_id = `${title.toString().replace(" ", "_")}_`

  // Create a new Google Task
  var task = {
    title: title,
    due: getFormattedDate(date)
  };
  console.log({ date, title, task })

  // Insert the task into the task list
  Tasks.Tasks.insert(task, listId);
}
function addEntry(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Sheet1"); // Replace "Sheet1" with the actual sheet name

  var sheet2 = spreadsheet.getSheetByName("Sheet2");

  // Get the data from cells D3 and D4 of Sheet1
  var data = sheet.getRange("D3:D4").getValues();
console.log("data[0]",data)
  // Add the data to the next available row in Sheet2
  sheet2.appendRow(["a","b",2]);
}
function convertSheetToPDFAndUpload() {
  //   var ui = SpreadsheetApp.getUi();
  // // Display a modal dialog box with custom HtmlService content.
  // var htmlOutput = HtmlService
  //   .createHtmlOutput('<img src=https://i.stack.imgur.com/AuqJU.gif>')
  //   .setWidth(250)
  //   .setHeight(300);
  // ui.showModalDialog(htmlOutput, 'Script Running');

  function getFormattedDate(d) {
    const date = new Date(d)
    let year = date.getFullYear();
    let month = (1 + date.getMonth()).toString().padStart(2, '0');
    let day = date.getDate().toString().padStart(2, '0');

    return day + '_' + month + '_' + year;
    // return date.toISOString()
  }

  function createblobpdf(sheetName, pdfName) {
    var sourceSpreadsheet = SpreadsheetApp.getActive();
    var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
    var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadsheet.getId() + '/export?exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
      + '&size=A4' // paper size legal / letter / A4
      + '&portrait=true' // orientation, false for landscape
      + '&fitw=true' // fit to page width, false for actual size
      + '&sheetnames=true&printtitle=false' // hide optional headers and footers
      + '&pagenum=RIGHT&gridlines=false' // hide page numbers and gridlines
      + '&fzr=false' // do not repeat row headers (frozen rows) on each page
      + '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
      + '&vertical_alignment=TOP' //TOP/MIDDLE/BOTTOM
      + '&gid=' + sourceSheet.getSheetId(); // the sheet's Id
    var token = ScriptApp.getOAuthToken();

    // request export url
    var response = UrlFetchApp.fetch(url, {
      headers: {
        'Authorization': 'Bearer ' + token
      }
    });

    var theBlob = response.getBlob().setName(pdfName);
    console.log({ theBlob: theBlob.getContentType() })

    return theBlob;
  };
  var folderName = "Khata"; // Replace with the desired folder name
  var drive = DriveApp;

  // Get the active spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var userName = spreadsheet.getRange("E6").getValue();
  const formatedName = `${userName.toString().replace(" ", "_")}_${folderName}_${getFormattedDate(new Date())}`
  console.log("formatedName", formatedName)
  // Get the desired sheet to convert
  var sheet = spreadsheet.getSheetByName("Sheet1"); // Replace "Sheet1" with the actual sheet name

  // CHECK if file exist 
  const file = drive.getFilesByName(formatedName + ".pdf")
  console.log("file.hasNext()", file.hasNext())
  if (file.hasNext()) {


    SpreadsheetApp.getUi().alert("Error : Invoice Already exist")
    console.log("ERRORRRRRRR", file.hasNext())

    return;
  } else {

    // Create a PDF blob from the sheet
    // var pdfBlob = sheet.copyTo("");
    // Create HTML content from the sheet
    // var htmlContent = sheet.copyTo();

    // Use the html2pdf library to convert HTML to PDF
    var pdfBlob = createblobpdf(sheet.getName(), formatedName);

    // Get the
    // Get the Drive service
    // Specify the folder name
    var folder
    // Check if the folder exists
    var folders = drive.getFoldersByName(folderName);
    console.log("folder.next", folders.hasNext())
    if (folders.hasNext()) {

      folder = folders.next()

    } else {
      folder = drive.createFolder(folderName);
    }

    try {
      // drive.get
      // Create a new file in Drive to store the PDF
      var newFile = drive.createFile(pdfBlob
      );
      newFile.moveTo(folder)
      console.log("newFile", newFile)

      // Rename the file to reflect the sheet name
      newFile.setName(formatedName + ".pdf");

      // Log the file ID for reference
      console.log("PDF file ID:", newFile.getId());
    } catch (err) {
      console.log({ err })
    }

  }
}

function onOpen() {
  // Create a menu item with the label "Add Task"
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Invoice')
    .addItem('Add Payment', "addTask")
    .addItem('Upload Invoice', "convertSheetToPDFAndUpload")
    .addItem('Upload entry', "addEntry")
    .addToUi();
}
Leave a Comment