1

Currently the step by step I use is as follows:

First step -> Create a PDF of the Page Jogos na TV from my spreadsheet:

function CreatePDF() {
  var ss = SpreadsheetApp.getActive();
  SpreadsheetApp.flush();
  var theurl = 'https://docs.google.com/a/mydomain.org/spreadsheets/d/' +
      'ID CODE TO SPREADSHEET' +
        '/export?format=pdf' +
          '&size=0' +
            '&portrait=true' +
              '&fitw=true' + 
                '&top_margin=0' +            
                  '&bottom_margin=0' +         
                    '&left_margin=0' +        
                      '&right_margin=0' +     
                        '&sheetnames=false&printtitle=false' +
                          '&pagenum=false' +
                            '&gridlines=false' +
                              '&fzr=FALSE' +
                                '&gid=' +
                                  'ID CODE TO SPREADSHEET PAGE';
  
  var token = ScriptApp.getOAuthToken();
  var docurl = UrlFetchApp.fetch(theurl, { headers: { 'Authorization': 'Bearer ' +  token } });
  var pdfBlob = docurl.getBlob();
  
  //...get token and Blob (do not create the file);
  
  var fileName = ss.getSheetByName("Jogos na TV").getRange("A1").getValue();
  
  //Access or create the 'PDF' folder;
  var folder;
  var folders = DriveApp.getFoldersByName("PDF");
  if(folders.hasNext()) {
      folder = folders.next();
    }else {
      folder = DriveApp.createFolder("PDF");
    }
  
  //Remove duplicate file with the same name;
  var existing = folder.getFilesByName(fileName);
  if(existing.hasNext()) {
      var duplicate = existing.next();
      if (duplicate.getOwner().getEmail() == Session.getActiveUser().getEmail()) {
        var durl = 'https://www.googleapis.com/drive/v3/files/'+duplicate.getId();
        var dres = UrlFetchApp.fetch(durl,{
          method: 'delete',
          muteHttpExceptions: true,
          headers: {'Authorization': 'Bearer '+token}
        });
        var status = dres.getResponseCode();
        if (status >=400) {
          
        } else if (status == 204) {
          folder.createFile(pdfBlob.setName(fileName));
        }
      }
    } else {
      folder.createFile(pdfBlob.setName(fileName));
    }
}

Second Step -> Manually copy the PDF link created in Google Drive

Step Three -> I send the text with the PDF minature to my group on Telegram:

function EnviarTelegram(botSecret, chatId, photoUrl, caption) {
  var response = UrlFetchApp.fetch("https://api.telegram.org/bot" + botSecret + "/sendPhoto?caption=" + encodeURIComponent(caption) + "&photo=" + encodeURIComponent(photoUrl) + "&chat_id=" + chatId + "&parse_mode=HTML");
}

The current formula for sending to Telegram via spreadsheet:

=EnviarTelegram("botSecret","chatId","Url to PDF","Programação de jogos na TV

"&TEXT('Jogos Hoje'!B1,"DD/MM/YYYY")&" e "&TEXT('Jogos Amanhã'!B1,"DD/MM/YYYY"))

The thumbnail created for the PDF is cut so the image sent to Telegram is also cut and the spreadsheets cannot be saved as an image, just document or PDF.

Is there any way to automatically convert PDF to image and be able to send to Telegram?

Digital Farmer
  • 1,705
  • 5
  • 17
  • 67

1 Answers1

2

Issue and workarounds:

Unfortunately, there are no methods for directly converting the PDF format to the image data in the methods of Google Apps Script. So, in this case, I thought that it is required to use the workarounds for achieving your goal.

Workaround 1:

In this workaround, the external API is used. When you want to directly convert the PDF data to an image data, how about using an external API? Ref

You can see the sample script for this at this thread.

Workaround 2:

In this workaround, the range of sheet is exported as an image. When I saw your Spreadsheet, it seems that the data range of a sheet in Google Spreadsheet is exported as a PDF data. From this situation, as the other workaround, how about converting the range of Spreadsheet to an image?

You can see the sample script for this at this thread.

Workaround 3:

In this workaround, the PDF data is sent with sendDocument. Ref In this case, it seems that the data is required to be sent as multipart/form-data. The sample script is as follows.

Sample script: 
var url = "https://api.telegram.org/bot" + botSecret + "/sendDocument?chat_id=" + chatId;
var blob = DriveApp.getFileById("### file ID of PDF file ###").getBlob();
var res = UrlFetchApp.fetch(url, {method: "post", payload: {document: blob}});
console.log(res.getContentText())

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Hello, in this issue the person who signed said that ```Drive.Files.export``` method can save spreadsheets as an image, but I also don't understand what the approach would be like, do you understand what his idea is? https://issuetracker.google.com/issues/238382847 – Digital Farmer Jul 12 '22 at 12:17
  • Maybe this is the solution to this need to save spreadsheets as an image without needing external services, but as I didn't understand the approach, I thought it would be interesting to show you. – Digital Farmer Jul 12 '22 at 12:18
  • @Digital Farmer Thank you for the information. Now, when I saw `exportFormats` in Drive API, I cannot find the mimeTypes of images. So, I'm interested in the method for exporting the Spreadsheet as an image. I think that `thumbnailLink` might be able to be used. But in this case, I thought that this cannot be retrieved the whole sheet when the sheet is large and also this is different from exporting of [Files: export method](https://developers.google.com/drive/api/v3/reference/files/export). How about this? – Tanaike Jul 12 '22 at 23:13