Apps script - export JSON-file from spreadsheet with replacement old file

user_15406421 0
Viewed 609

I need a little help with my script, I wrote by help from internet.

I export data from spreadsheet to json-file and save it on google-drive, but every new file doesn't replace old file. Script creates new file with ending at name of new file (1), (2) etc:

  • exp_planning.json - this file I need
  • exp_planning(1).json - this file and other with I not need

Be happy for your help! Thank you, friends!))

function convertSheet2JsonText(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, 15);
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getRange("A2:A").getValues().filter(String).length;
  var rowValues = [];
  for(var rowIndex = 2; rowIndex <= lastRow; rowIndex ++ ) {
    var strRange = sheet.getRange(2, 1, rowIndex, 1).getValue();

    if(strRange != "") {
      var colStartIndex = 1;
      var rowNum = 1;
      var range = sheet.getRange(rowIndex, colStartIndex, rowNum, 15);
      var values = range.getValues();
      rowValues.push(values[0]);
    }
  }

  // create json
  var jsonArray = [];
  for(var i=0; i<rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();

    for(var j=0; j<titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  //return jsonArray;
  Logger.log(jsonArray);

  var blob,file,fileSets,obj;
  var folder = DriveApp.getFoldersByName('JSON_EXPFILES').next();
    fileSets = {
      title: 'exp_planning.json',
      mimeType: 'application/json',
      "parents": [
        {
          "id": folder.getId(),
          "kind": "drive#parentReference"
        }
      ]
    };

  blob = Utilities.newBlob(JSON.stringify(jsonArray), "application/vnd.google-apps.script+json");
  file = Drive.Files.insert(fileSets, blob);
  Logger.log('ID: %s, File size (bytes): %s, type: %s', file.id, file.fileSize, file.mimeType);
}

function doGet() {

  var sheetName = "для ТК";
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var json = convertSheet2JsonText(sheet);

  return ContentService
    .createTextOutput(JSON.stringify(json))
    .setMimeType(ContentService.MimeType.JSON);
}

I found one way with deleting old file before creating new...

  var allFiles = folder.getFilesByName(fileSets.title);

  while (allFiles.hasNext()) {
    file = allFiles.next();
    file.getParents().next().removeFile(file);
  }

but may be there is anything else about replacement old file?

google-drive-api google-apps-script google-sheets json
1 Answers

I believe your goal as follows.

  • When you create a new file of exp_planning.json in the specific folder, you want to remove the existing files, which are the same filename, in the specific folder. And then, you want to create new file to the folder.

Modification points:

  • In your script, the file of exp_planning.json is created to the specific folder using Drive.Files.insert(). In this case, the files with the same filename are created. So, it is required to check the existing files and create the new file. But, when I saw the script of I found one way with deleting old file before creating new..., removeFile of Class Folder is used. This had been used for removing the given file from the current folder. In the current stage, this method was deprecated. Ref
  • When you want to remove the file, you can use setTrashed of Class File or the method of "Files: delete" in Drive API.
  • And, at blob = Utilities.newBlob(JSON.stringify(jsonArray), "application/vnd.google-apps.script+json");, when you want to give the mimeType, please use application/json.

When above points are reflected to your script, it becomes as follows.

Modified script:

From: 
var folder = DriveApp.getFoldersByName('JSON_EXPFILES').next();
  fileSets = {
    title: 'exp_planning.json',
    mimeType: 'application/json',
    "parents": [
      {
        "id": folder.getId(),
        "kind": "drive#parentReference"
      }
    ]
  };

blob = Utilities.newBlob(JSON.stringify(jsonArray), "application/vnd.google-apps.script+json");
file = Drive.Files.insert(fileSets, blob);
To: 
var folder = DriveApp.getFoldersByName('JSON_EXPFILES').next();
var filename = 'exp_planning.json';
var files = DriveApp.getFilesByName(filename);
while (files.hasNext()) {
  files.next().setTrashed(true);
}
// Drive.Files.emptyTrash();  // If you want to empty the trash box, you can also use this. But when you use this, please be careful this.
fileSets = {
  title: filename,
  mimeType: 'application/json',
  "parents": [
    {
      "id": folder.getId(),
      "kind": "drive#parentReference"
    }
  ]
};
blob = Utilities.newBlob(JSON.stringify(jsonArray), 'application/json');
file = Drive.Files.insert(fileSets, blob);
  • If you want to empty the trash box, you can also use Drive.Files.emptyTrash(). But when you use this, please be careful this.

Note:

  • In this modified script, it supposes that jsonArray is the correct value you expect. Please be careful this.

  • When you want to overwrite the existing file of exp_planning.json, please modify above script of "From:" as follows. In this modification, when the file is existnig, the file is overwritten by blob using the method of "Files: update". Ref

      var folder = DriveApp.getFoldersByName('JSON_EXPFILES').next();
  var filename = 'exp_planning.json';
  var files = DriveApp.getFilesByName(filename);
  blob = Utilities.newBlob(JSON.stringify(jsonArray), 'application/json');
  if (files.hasNext()) {
    file = Drive.Files.update({}, files.next().getId(), blob);
    // or DriveApp.getFileById(files.next().getId()).setContent(JSON.stringify(jsonArray));
  } else {
    fileSets = {
      title: filename,
      mimeType: 'application/json',
      "parents": [
        {
          "id": folder.getId(),
          "kind": "drive#parentReference"
        }
      ]
    };
    file = Drive.Files.insert(fileSets, blob);
  }

References:

user_71086530
Related