I've been trying to extract data from Excel file stored in Google Drive. Here's what I've done so far:
function readExcelFromDrive(file_id='1_m2AR8UVOpn9XABPz_WvyPkLzz9OgyPs',debug=true) {
let file = DriveApp.getFileById(file_id);
console.log(`File ${file_id} found.`)
let blob = file.getBlob();
let config = {
title: "[Auto Generated Google Sheets] " + file.getName(),
parents: [{id: file.getParents().next().getId()}],
mimeType: MimeType.GOOGLE_SHEETS
};
let spreadsheet = DriveApp.createFile(blob).setSharing(DriveApp.Access.ANYONE,DriveApp.Permission.EDIT)
console.log(`Temp spreadsheet created. Id: '${spreadsheet.getId()}'`)
let ss = SpreadsheetApp.openById(spreadsheet.getId())
console.log(`Temp spreadsheet opened.`)
let data = ss.getActiveSheet().getDataRange().getValues()
console.log(data)
}
how do i resolve this?
Thanks in advance
spreadsheet
is the XLSX file. Also, config
is not used.When these points are reflected in your script, how about the following modification?
Before you use this script, please enable Drive API v3 at Advanced Google services.
function readExcelFromDrive((file_id='1_m2AR8UVOpn9XABPz_WvyPkLzz9OgyPs',debug=true) {
let file = DriveApp.getFileById(file_id);
console.log(`File ${file_id} found.`);
let blob = file.getBlob();
// --- I modified the below script.
let config = {
name: "[Auto Generated Google Sheets] " + file.getName(),
parents: [file.getParents().next().getId()],
mimeType: MimeType.GOOGLE_SHEETS
};
const { id } = Drive.Files.create(config, blob);
let spreadsheet = DriveApp.getFileById(id).setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT);
// ---
console.log(`Temp spreadsheet created. Id: '${spreadsheet.getId()}'`);
let ss = SpreadsheetApp.openById(spreadsheet.getId());
console.log(`Temp spreadsheet opened.`);
let data = ss.getActiveSheet().getDataRange().getValues();
console.log(data);
}