CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user2748807
user2748807

Reputation: 109

Copy sheet to new spreadsheet, Add cell value to end of new spreadsheet name

This is now creating the copy and adding the text to the end of the file name. The new file is located in the same directory as the original file. I can't seem to move the new file to the "Timesheet Archive" folder. Seems like I should just be able to specify the location for the new file?

var range = sheet.getRange(2,7); 
var data = range.getValue();
var SSID = '1S3Vj5lYOchh0OJ5ZCamsoMCKtcTHeFd72nyxPHJYofk'
var CopyDate = Utilities.formatDate(data , "GMT-8", "yy_MM_dd"); 
var folder = DriveApp.getFoldersByName('Timesheet Archive'); 
var id = DriveApp.getFileById(SSID)
id.makeCopy(SpreadsheetApp.openById(SSID).getName() + "_" + CopyDate);

I am moving to Google's new Sheets and am having trouble with this function which was working fine prior to the move:

function exportData() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Timesheet");
  var sourceData = sourceSheet.getDataRange().getValues();
  sourceData.splice(0,1);  // Remove header

  var targetSS = SpreadsheetApp.openById("1ZKQw9WnAFmJfC6GP_CYmSbQK_820zIR6oQGnLMc1yBM").getSheetByName("Master Time Sheet");
  var targetRangeTop = targetSS.getLastRow(); // Get # rows currently in target
  targetSS.getRange(targetRangeTop+1,1,sourceData.length,sourceData[0].length).setValues(sourceData);

  var sheet = SpreadsheetApp.getActive().getSheetByName('Timesheet');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var name = ss.getName();  // Not necessary 

  var sheet = ss.getSheetByName('Timesheet'); // or whatever is the name of the sheet 
  var range = sheet.getRange(2,7); 
  var data = range.getValue();

  var SSID = '1S3Vj5lYOchh0OJ5ZCamsoMCKtcTHeFd72nyxPHJYofk'
  var CopyDate = Utilities.formatDate(data , "GMT-8", "yy_MM_dd"); // Function Date + Format

  var folder = DocsList.getFolder('Timesheet Archive'); //Use this line if you want to get the folder by name
  var backup = DocsList.getFileById(SSID).makeCopy(SpreadsheetApp.openById(SSID).getName() + "_" + CopyDate);

  backup.addToFolder(folder); //This line will move the file
  backup.removeFromFolder(DocsList.getRootFolder()); //This line is needed to remove the File from the Root
}

The first part copies part of the sheet to another existing spreadsheet and this part of the function is working properly. The second part copies the entire sheet, creates a new file using this copied sheets name and data and appends the end of the file name using text (a date) from a cell located on the source sheet. It is supposed to first move it to the root folder and then to another folder titled "Timesheet Archive".

I am not getting any errors and the file is not appearing in either the root folder nor in the "Timesheet Archive" folder.

Upvotes: 1

Views: 570

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

You are using:

DriveApp.getFoldersByName('Timesheet Archive');

That gets a "Folder Iterator"

You need to either use:

getFolderById(id)

Or iterate through the folders, (Even though there may only be one!)

while (folders.hasNext()) {
  var folder = folders.next();
  if (folder === 'Timesheet Archive') {
    //Make copy here
    
  };
}

There is no addToFolder() method of the File Class. backup is a file. The makeCopy() method has a destination option. You can create the backup and put it into the destination all at the same time.

https://developers.google.com/apps-script/reference/drive/file#makeCopy(String,Folder)

Use your folder variable, that is a folder type as the destination.

Upvotes: 1

Related Questions