CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Cyril N.
Cyril N.

Reputation: 39869

Google Script for Spreadsheet : How to keep value saved for "ever"?

I'm building a Google Script for my Spreadsheets, and the script queries an external API that requires payment every time it is queried.

I'd like my script to keep in memory the value if it has been already loaded (meaning if I didn't changed the other cells used by this script), in order to avoid querying the API every time I load the page.

I thought about using the Cache, especially the CacheService.getDocumentCache(), but it's not a long term storage.

Is there a better alternative ?

Upvotes: 1

Views: 1943

Answers (2)

Kriggs
Kriggs

Reputation: 3778

A bit faster than SpreadsheetApp is to save as a JSON in Google Drive:

function saveFile(){
  var transactions = {'[email protected] ': 1238190238, '[email protected]' : 1238190238, '[email protected]' : 1238190238};
  return DriveApp.createFile('Transactions', JSON.stringify(transactions ));
}

function getFile(){
  var file = DriveApp.getFilesByName('Transactions').next();
  return JSON.parse(file.getAs("application/none").getDataAsString());
}

Upvotes: 1

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17752

You can use a spreadsheet itself as long-term storage. Save the parameters used and the return values there and check it every time before querying your external API.

If it's not too much data, the Properties Services may also suit you. But if it's "too much" data it'll not work. Well, even a Spreadsheet has some constraints. In such case, you could try using a regular file in Google Drive as storage or an external database via JDBC.

But if you need to get there your script will probably start hitting other Apps Script quotas that may prevent you to use it at all.

Upvotes: 1

Related Questions