1

I have a google sheet having 110,000 rows and 10 columns.

If I try to export using the normal File > Download > As PDF, it takes forever to make the Save button work (The ~4800 pages of the PDF keeps rendering and on a premature save, nothing happens) I have done this on Chrome, Firefox and Edge browser, just to see if changing browser would make a difference.

Then I tried using app-script, but the ones available on the WebApp StackExchange did not work.

I know the solution will be with an app script code. What is the best solution for me to able to export the 1 million cell sized sheet to a PDF easily.

EDIT:

As per the comments below I did execute the 8 line code in the newer app script console of Google,

function saveAsPDF() {
const folderName = `Cubing`;
const fileNamePrefix = `filename`;

DriveApp.getFoldersByName(folderName)
.next()
.createFile(SpreadsheetApp.getActiveSpreadsheet()
              .getBlob()
              .getAs(`application/pdf`)
              .setName(`${fileNamePrefix} - ${Utilities.formatDate(new 
 Date(), `GMT-8`, `yyyy-MM-dd`)}`));  
 }

But I got a message which is not related to the code.

The execution log said,

Exception: We're sorry, no servers are currently available. Please wait a bit and try again.
saveAsPDF   @ Code.gs:7
6
  • I tried to run the scripts discussed in the thread, stackoverflow.com/questions/21997924/… – abunickabhi Dec 14 '20 at 15:15
  • 2
    Please be more descriptive. Do you get error messages on all the scripts? (please edit the question and add the textual error message, I suggest you to focus on one of the scripts) – Rubén Dec 14 '20 at 15:51
  • 2
    The author's credentials of those scripts are impeccable and I agree with focusing/troubleshooting on one of those scripts. But at the risk of mudding the waters, the scripts date from 2014/2015 and Apps Script has changed a lot. A fast Google (only results in the 'last year') raised Can google sheets save a PDF copy onto a selected Drive folder nightly?; just eight lines of code, and it works nicely. Whether it suits you is another matter but I would like to see you using more recent precedents. @Rubén may have a view. – Tedinoz Dec 15 '20 at 3:30
  • 2
    In my (limited) experience, this is a "there's-an-error-somewhere" kind of error; however it is giving you a big hint here: "@ Code.gs:7". The values of your variables (const folderName, const fileNamePrefix and const fileNamePrefix) are are defined between backticks rather than single (or double) quotes. Also .setName(${fileNamePrefix} - ${Utilities.formatDate(new Date(), GMT-8, yyyy-MM-dd)})); contains more backticks and is presenting as two lines rather than one long line. Try those changes and see what happens. – Tedinoz Dec 17 '20 at 11:33
  • 1
    @Tedinoz I suggest you to post this kind of comments as answers (even if another user, including me, posted an answer). – Rubén Dec 17 '20 at 20:36
2

There is a ` pair-matching problem:

1
|
v
`${fileNamePrefix} - ${Utilities.formatDate(new 
 Date(), `GMT-8`, `yyyy-MM-dd`)}`
         ^     ^  ^          ^  ^
         |     |  |          |  |
         1     2  2          3  3    

While there are multiple ways to fix your script, what about this (use the + to concatenate values and regular strings instead of template literals):

fileNamePrefix + ' - ' + Utilities.formatDate(new Date(), 'GMT-8', 'yyyy-MM-dd')

Resources

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.