覆盖文件Google Sheets脚本

Question

我有一个Google Sheet API脚本。我想要1。导出到csv 2。覆盖现有文件。

我想覆盖驱动器文件夹中的现有文件。现在，它正在复制文件。如何覆盖？

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);
}

Answer 1

好。您似乎需要将CSV数据推送到当前工作表。您可以使用高级纸张服务

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;
}

如果有人需要导出到CSV

因此，如果它是一个真正的文件，那么你可以

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

如果是床单。然后在所有之前，你必须将文件导出到CSV。之后您可以setContent。

对于样品，这很好

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());
}

基于摘录表/导出导入到csv