Write and retrieve JSON file to Google Drive using Google Apps Script

March 24, 2022

I am writing a program that takes in the data from a large Google Sheet, treats and manipulates it and makes connection to other sheets in the same file. In order to improve my runtime and make a backup database.

I would like to store the data to a JSON file somewhere that I can easily use in Google Apps script again (I believe Google Drive is the best free option). How do I accomplish this?

I appreciate if you can show the exact codes that saves the following file to Google Drive and then reads it back into the script.

let listA = [{id: 34, performance: 200, supervisor: 'A', name: 'Aethelwich'},
    {id: 35, performance: 300, supervisor: 'B', name: 'Aethelram'},
    {id: 36, performance: 400, supervisor: 'A', name: 'Aethelham'},
    {id: 41, performance: 500, supervisor: 'A', name: 'Aethelfred'}];

let jsonObject = JSON.stringify(listA);

// 1. code to save this jsonObject to Google Drive
// 2. code to retrieve the same saved JSON file back from google drive

>Solution :

In order to achieve your goal, how about the following sample script?

Sample script:

let listA = [{ id: 34, performance: 200, supervisor: 'A', name: 'Aethelwich' },
{ id: 35, performance: 300, supervisor: 'B', name: 'Aethelram' },
{ id: 36, performance: 400, supervisor: 'A', name: 'Aethelham' },
{ id: 41, performance: 500, supervisor: 'A', name: 'Aethelfred' }];

let jsonObject = JSON.stringify(listA);

// Save the data as a file.
const file = DriveApp.createFile("sample.txt", jsonObject); // Or, if you want to put the file to the specific folder, please use DriveApp.getFolderById("folderID").createFile("sample.txt", jsonObject)
const fileId = file.getId();
console.log(fileId)

// Retrieve the data from the file.
const text = DriveApp.getFileById(fileId).getBlob().getDataAsString(); // please set the file ID of the saved file.
const array = JSON.parse(text);
console.log(array)