print the first row(frozen row) and any range from the sheet under that row in google sheet-CodePudding

i want to print some data from a sheet that have a frozen row, and i want to print any range of data but i want the first row to get printed automatically every time i print something and i want to that with a button and here i mean with example:

the first row is frozen and i want to print a range from a4 to d6 i want to have two cells and a button when i enter the range a4 and d6 to the two cells and press the button i get a pdf file contain the frozen row and the range i entered in the two cells (a4 and d6) is there any way to do that?

i tried a different method using the apps script but it was not flexible because i only can change the values from the code and here is the code i tried :

function print() {

  var spreadsheetsToProcess = [
    {
      spreadsheetId: SpreadsheetApp.openByUrl("URL"),
      sheetName: "items",
      rangeA1Notation: "A1:D1"
    },
    {
      spreadsheetId: SpreadsheetApp.openByUrl("URL"),
      sheetName: "items",
      rangeA1Notation: "A2:D6"
    }
  ];

  var file = getPDF(spreadsheetsToProcess);

  MailApp.sendEmail("****@GMAIL.com", 'Attachment example', 'Two spreadsheets at once.', {attachments:[file]});
}

function getPDF(spreadsheets) {
  
  var html = "<h2>"  ss.getSheetByName('items').getName()  "</h2>";
  spreadsheets.forEach(spreadsheet => {
    var ss = SpreadsheetApp.openByUrl("URL");
    var sheet = ss.getSheetByName(spreadsheet.sheetName);
    var data = sheet.getRange(spreadsheet.rangeA1Notation).getValues();

    html  = "<h3></h3><table style=' border: 1px solid yellow'>";
    data.forEach(row => {
      html  = "<tr style='width: 175px ;border: 1px solid black'>";
      row.forEach(cell => {
        html  = `<td style='width: 175px ;border: 1px solid black'>${cell}</td>`;
      })
      html  = "</tr>";
    });
    html  = "</table><br/>";
  })
  

  var output = HtmlService.createHtmlOutput(html);
  var pdf = output.getAs('application/pdf').setName("export.pdf");

  return pdf;
}

i hope i was clear about everything

thank you in advance.

CodePudding user response：

From the following your sample situation,

the first row is frozen and i want to print a range from a4 to d6 i want to have two cells and a button when i enter the range a4 and d6 to the two cells and press the button i get a pdf file contain the frozen row and the range i entered in the two cells (a4 and d6) is there any way to do that?

I believe your goal is as follows.

You want to run your script of print() by giving the range values from 2 cells.

When I saw your script,

I think that an error occurs at var html = "<h2>" ss.getSheetByName('items').getName() "</h2>";. Because ss is not declared.
It seems that spreadsheetId: SpreadsheetApp.openByUrl("URL"), is not used.
From your sample situation, I thought that the value of spreadsheetsToProcess can be used as a JSON object instead of an array.

In this case, how about the following modification?

Modified script:

Before you use this script, please set the range values of "A4" and "D6" to the cells "E1:F1", respectively. And, please set the email address to MailApp.sendEmail. And, run the function of print(). If you want to run this script by clicking a button on Spreadsheet, please assign print to the button.

function print() {
  var url = "https://docs.google.com/spreadsheets/d/###/edit"; // Please set your Spreadsheet URL. This is from your showing script.
  var valueRange = "E1:F1"; // As a sample, the values of range are retrieved from "E1:F1". Please modify this for your actual situation.

  var ss = SpreadsheetApp.openByUrl(url);
  var [a, b] = SpreadsheetApp.getActiveSheet().getRange(valueRange).getValues()[0];
  var spreadsheetsToProcess = { sheetName: "items", rangeA1Notation: a   ":"   b };
  var file = getPDF(ss, spreadsheetsToProcess);
  MailApp.sendEmail("###", 'Attachment example', 'Two spreadsheets at once.', { attachments: [file] });
}

function getPDF(ss, { sheetName, rangeA1Notation }) {
  var sheet = ss.getSheetByName(sheetName);
  var data = sheet.getRange(rangeA1Notation).getValues();
  var html = "<h2>"   sheetName   "</h2>";
  html  = "<h3></h3><table style=' border: 1px solid yellow'>";
  data.forEach(row => {
    html  = "<tr style='width: 175px ;border: 1px solid black'>";
    row.forEach(cell => {
      html  = `<td style='width: 175px ;border: 1px solid black'>${cell}</td>`;
    })
    html  = "</tr>";
  });
  html  = "</table><br/>";
  var output = HtmlService.createHtmlOutput(html);
  var pdf = output.getAs('application/pdf').setName("export.pdf");
  return pdf;
}

When you run this script, the script of print() is run by retrieving the range values of "A4" and "D6" from the cells "E1:F1".

CodePudding user response：

HERE, I GOT IT TO WORK LOOK AT THE CODE @Tanaike

function print() {
  

  var spreadsheetsToProcess = [
    {
      spreadsheetId: SpreadsheetApp.openByUrl("URL"),
      sheetName: "items",
      rangeA1Notation: "A1:D1"
    }
    
  ];

  var file = getPDF(spreadsheetsToProcess);

  MailApp.sendEmail("****@GMAIL.com", 'Attachment example', 'Two spreadsheets at once.', {attachments:[file]});


function getPDF(spreadsheets) {
  var html = "<h2>"  sheetName  "</h2>";
  spreadsheets.forEach(spreadsheet => {
    var ss = SpreadsheetApp.openByUrl("URL");
    var sheet = ss.getSheetByName(spreadsheet.sheetName);
    var data = sheet.getRange(1,1,1,4).getValues();
    
    

    html  = "<h3>Headers</h3><table style=' border: 1px solid yellow'>";
    data.forEach(row => {
      html  = "<tr style='width: 200px ;border: 1px solid black'>";
      row.forEach(cell => {
        html  = `<td style='width: 200px ;border: 1px solid black'>${cell}</td>`;
      })
      html  = "</tr>";
    });
    html  = "</table><br/>";
  })

  spreadsheets.forEach(spreadsheet => {
    var ss = SpreadsheetApp.openByUrl("url");
    var sheet = ss.getSheetByName(spreadsheet.sheetName);
    var data1 = sheet.getRange(2,1,5,4).getValues();
    

    html  = "<h3>Data</h3><table style=' border: 1px solid yellow'>";
    data1.forEach(row => {
      html  = "<tr style='width: 200px ;border: 1px solid black'>";
      row.forEach(cell => {
        html  = `<td style='width: 200px ;border: 1px solid black'>${cell}</td>`;
      })
      html  = "</tr>";
    });
    html  = "</table><br/>";
  })
  

  var output = HtmlService.createHtmlOutput(html);
  var pdf = output.getAs('application/pdf').setName("export.pdf");

  return pdf;

  
}}