How can I copy data from a google spreasheet using the file name, and paste it into another file?

user_14106868 0
Viewed 40

I am trying to use a file name to get data from a Spreadsheet, and copy the data into a different target file. I have the following code, and it is failing at line 7. When I manually put in an ID in line 7 the code works. Could someone help? Thank you very much.

function openFile() {
  var fileList = DriveApp.getFilesByName('file name')
  while (fileList.hasNext()) {
  Logger.log(fileList.next().getId())
}
var fileID = Logger.getLog();
var ss = SpreadsheetApp.openById(fileID);//fails here
  var source = ss.getSheetByName('sheet name');
var rangeSource = source.getDataRange();
var data = rangeSource.getValues();
var rowsInData = data.length;
var colsInData = data[0].length;
var sss = SpreadsheetApp.openById('1Hia3_zGk1qyPlVuPTQR5iOjExFaMEe3EQOMXBc2C6eI');
var logSheet = sss.getSheetByName("diamondbackprogress");
var rowsInLogSheet = logSheet.getMaxRows();
var colsInLogSheet = logSheet.getMaxColumns();

if (rowsInLogSheet < rowsInData) {
  logSheet.insertRowsAfter(rowsInLogSheet, rowsInData - rowsInLogSheet);
} else if (rowsInLogSheet > rowsInData) {
  logSheet.deleteRows(rowsInData, rowsInLogSheet - rowsInData);
}

if (colsInLogSheet < colsInData) {
  logSheet.insertColumnsAfter(colsInLogSheet, colsInData - colsInLogSheet);
} else if (colsInLogSheet > colsInData) {
  logSheet.deleteColumns(colsInData, colsInLogSheet - colsInData);
}  
logSheet.getRange(1, 1, rowsInData, colsInData).setValues(data); 
}
google-apps-script javascript
1 Answers

Logger.getLog() won't give you the id of the file, it will give a list of messages in the current log. See here for more details.

Replace this part:

function openFile() {
var fileList = DriveApp.getFilesByName('file name')
while (fileList.hasNext()) {
  Logger.log(fileList.next().getId())
}
var fileID = Logger.getLog();

with this:

function openFile() {
  var fileList = DriveApp.getFilesByName('file name')
  while (fileList.hasNext()) {
     var fID = fileList.next().getId();
}
var fileID = fID;
user_112252910
Related