Overwrite file Google Sheets Script

Question

I have a Google Sheet API Script. I want to 1. Export to csv 2. Overwrite existing file.

I want to overwrite the existing file in my drive folder. Right now, its making copies of the file. How I can overwrite ?

function makeCopy() {

// generates the timestamp and stores in variable formattedDate as year-month-date hour-minute-second
var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");

// gets the name of the original file and appends the word "copy" followed by the timestamp stored in formattedDate
var name = SpreadsheetApp.getActiveSpreadsheet().getName();


var destination = DriveApp.getFolderById("1JGHoshv6");

// gets the current Google Sheet file
var file = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())

// makes copy of "file" with "name" at the "destination"
file.makeCopy(name, destination);
}

Can I ask you about your question? You want to overwrite the existing CSV file by new CSV data using Google Apps Script. I could understand your goal like this. If my understanding is correct, can I ask you about the relationship between your script and your goal? And also, where do you want to retrieve the CSV data? I couldn't understand about your script. — Tanaike
My code is in the google sheets file : Google sheet ->Tools ->Script editor. this export is done every hour with a trigger. So I don't want duplicates files in my Google Drive. I want one updated export every hour... — Simon GIS
Thank you for replying. From your replying, the target file is not CSV file, it is Google Spreadsheet? — Tanaike
Thank you for replying. From your replying, you want to overwrite the existing CSV file by new CSV data using Google Apps Script. Is my understanding correct? — Tanaike
Thank you for replying. I could understand that the destination file is the CSV file. But I couldn't understand about the source data. I apologize for my poor English skill. But I think that the contributorpw's answer will resolve your issue. — Tanaike

contributorpw contributorpw · Accepted Answer · 2020-04-14T04:47:43

OK. It seems you need push a CSV data to the current sheet. You can use Advanced Sheets Service

var CSV_FILE_ID = '1G9eI5bj_jYQz_a_mk4t5mmLJsgwOpP8q'; // Your CSV file or data
var SHEETID = '0';

/**
 * User action. Import the CSV file
 */
function userActionImportFromCSV() {
  var res = importFromCSV_(
    CSV_FILE_ID,
    SpreadsheetApp.getActive().getId(),
    SHEETID
  );
  Logger.log(res);
}

/**
 * Imports the CSV file to the Sheet
 * @param {string} csvId CSV file Id
 * @param {string} spreadsheetId  Spreadsheet Id
 * @param {string} sheetId Sheet Uid
 * @return {any}
 */
function importFromCSV_(csvId, spreadsheetId, sheetId) {
  // Get CSV data

  var data = DriveApp.getFileById(csvId)
    .getBlob()
    .getDataAsString();

  // Clear the Sheet
  var updateCellsRequest = Sheets.newUpdateCellsRequest();
  updateCellsRequest.fields = 'userEnteredValue';
  updateCellsRequest.range = { sheetId: sheetId };
  batchUpdateSpreadsheet_(
    {
      updateCells: updateCellsRequest,
    },
    spreadsheetId
  );

  // Paste the data
  var pasteDataRequest = Sheets.newPasteDataRequest();
  pasteDataRequest.coordinate = {
    sheetId: SHEETID,
    rowIndex: 0,
    columnIndex: 0,
  };
  pasteDataRequest.data = data;
  pasteDataRequest.type = SpreadsheetApp.CopyPasteType.PASTE_VALUES;
  pasteDataRequest.delimiter = ',';

  var batchUpdateSpreadsheetResponse = batchUpdateSpreadsheet_(
    {
      pasteData: pasteDataRequest,
    },
    spreadsheetId
  );
  return batchUpdateSpreadsheetResponse;
}

/**
 *
 * @param {object} request Request object
 * @param {string} spreadsheetId Spreadsheet Id
 * @return {object} BatchUpdateSpreadsheetResponse
 */
function batchUpdateSpreadsheet_(request, spreadsheetId) {
  var resource = {
    requests: [],
  };
  resource.requests.push(request);
  var batchUpdateSpreadsheetResponse = Sheets.Spreadsheets.batchUpdate(
    resource,
    spreadsheetId
  );
  return batchUpdateSpreadsheetResponse;
}

If some one need export to CSV also

So if it is a really a real file then you can

function overWriteFileSample() {
  const file = DriveApp.getFileById('ABCD1234'); // Your CSV file
  const content = new Date().toISOString();
  file.setContent(content);
}

If it's a Sheet. Then before all you have to export the file to CSV. After that you can setContent.

For sample this works fine

function run(){
  exportToCSV_('ABCD1234', spreadsheetId, sheetId)
}


/**
 *
 * @param {string} csvId
 * @param {string} spreadsheetId
 * @param {string | number} sheetId
 */
function exportToCSV_(csvId, spreadsheetId, sheetId) {
  var url = Utilities.formatString(
    'https://docs.google.com/spreadsheets/export?id=%s&exportFormat=csv&gid=%s',
    spreadsheetId,
    sheetId
  );

  var data = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
  }).getBlob();

  DriveApp.getFileById(csvId).setContent(data.getDataAsString());
}

Based on the snippet sheets/export-import-to-csv

Overwrite file Google Sheets Script

1 Answers

If some one need export to CSV also