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
Improve this question
6
  • I tried to run the scripts discussed in the thread, stackoverflow.com/questions/21997924/…
    – abunickabhi
    Commented Dec 14, 2020 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 - Volunteer Moderator - ♦
    Commented Dec 14, 2020 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
    Commented Dec 15, 2020 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
    Commented Dec 17, 2020 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 - Volunteer Moderator - ♦
    Commented Dec 17, 2020 at 20:36

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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