0

I've currently got an editable Google Sheet with data in specific cells and a clean, mostly static template Google Doc file with placeholder text to receive values from Google Sheet data which I then save to a PDF.

At the moment, I do the following to create a copy of the template Google Doc into a temp folder, ready to replace the text placeholders in the Doc with values from the Sheet:

let newCMRbody = DocumentApp.openById(DriveApp.getFileById(cmrTemplateId).makeCopy(tempFolder).getId()).getBody();

Doing the above takes around 4-5 seconds which isn't a lot but combined with other calls in the script is causing noticeable delays.

Is there a way to store a copy of the template Google Doc as a variable in the script so that it doesn't have to go out and get it each time? Or can the value be stored in a cache?

When I do: Logger.log(JSON.stringify(newCMRbody)); It's just showing an empty object: {}

Diagonali
  • 109
  • 1
  • 10
  • I replicated your code, I also got `{}` when using `Logger.log(JSON.stringify(newCMRbody));` but the variable is not empty. By the way, I have some clarifications, I got confused with your goal. Originally you are creating a copy of a template document and you aims to update its body. But if you plan to save it in the cache, the you will be updating the same document over and over until it was removed in the cache. Is that what you want to achieve? – Ron M Jul 06 '21 at 16:00
  • @RonM Interesting the ```{}``` isn't empty. The issue is that retrieving the template document seems to take a while (```getFileById```). Making a copy of it is a separate process (I think). The copies won't be cached since they're being amended with replacements to placeholders, saved as PDF and then deleted.So since the template will rarely change I'm trying to speed up the process of getting the template. I've tried to store the code for the template in the script itself as a variable and also in the Cache Service but it doesn't seem to like the format of the template document. – Diagonali Jul 07 '21 at 08:54
  • When I tested it on my side, `getFileById` doesn't took a lot of time. It is the creation of a new copy that is taking around 4s of time. It is not possible to cache a File class object using Cache service since it can only save a string type of data. – Ron M Jul 08 '21 at 16:51
  • How about replicate the format in your template doc in your Google sheets, and just export that sheet tab as pdf file? It would save you time creating,updating and converting a copy of a template doc to pdf. Something like this [sample post](https://stackoverflow.com/a/68181851/14606046) – Ron M Jul 08 '21 at 16:58
  • Or you might want to check your other code for optimizations. Since if you only create a copy of a template file once, then update and convert it to PDF, I don't think this 4-5s copy creation will have that huge impact in your process? – Ron M Jul 08 '21 at 17:13
  • @RonM Thanks for your suggestions. Good to know it's the creation of a new copy that's taking a bit of time. I can't quite do what you say though because the template doc is a Google Doc in a specific layout with {placeholders} in it. So I'm taking the data from the Spreadsheet and using that to create the Google Doc. The Spreadsheet needs to be in a completely different format. I'm now looking at the Docs.Documents.batchUpdate() function to see if I can update all placeholders as once but I'm stuck with the JSON request object structure on that unfortunately. – Diagonali Jul 11 '21 at 10:41

0 Answers0