CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Gubernaculum
Gubernaculum

Reputation: 17

GoogleSheets with GoogleApps. A little loss with Creating and copying

A month into google apps/googlesheets. I've got some of the basics down, however struggling to put a lot of basic concepts together.

Step 1) Create and check if Spreadsheet exists in folder. If it doesn't exist create one based on the name in Cell A1 and COPY ActiveSpreadsheet() data to that new FILE with sheet name TODAY() date.

Step 2) If a spreadsheet with name exists, copy from ActiveSpreadsheet() to the spreadsheet named in Cell A1 with a NEW SHEET named after today's date.

So far I have got pieces of stuff together but I am MISSING basic knowledge of trying to put it altogether. Sorry if its a COMPLETE mess I'm trying to piece it together as I go. ANY HELP WILL be appreciated or websites/resources to lead me in the right direction.

 function saveAsSpreadsheet(){ 
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange('Sheet1!A1');
  var destFolder = DriveApp.getFolderById("XXXXXXXXXX"); 
  DriveApp.getFileById(sheet.getId()).makeCopy(NAME CHECK, destFolder); 

  if( ss.getSheetByName('') == null)
  //if returned null means the sheet doesn't exist, so create it
  var val = Utilities.formatDate(new Date(A2), "GMT+1", "MM/dd/yyyy");
  ss.insertSheet(val, ss.getSheets().length, {template: templateSheet});
  sheet2 = ss.insertSheet(A2);

  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName(new Date(A2));
  var C2=sh1.getRange('C2').getValue();
  var L2=sh1.getRange('L2').getValue();
  sh2.appendRow([new Date(),C2,L2]);

}

Upvotes: 1

Views: 58

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14502

Probably you need something like this:

function main() {

  // get current date
  var date = Utilities.formatDate(new Date(), 'UTC', 'YYYY-MM-dd');

  // get current spreadsheet
  var cur_ss = SpreadsheetApp.getActiveSpreadsheet();

  // get folder
  var folder = DriveApp.getFolderById('###'); // <--- your folder ID goes here 
  
  // get file name from the cell 'Sheet1!A1'
  var name = cur_ss.getSheetByName('Sheet1').getRange('A1').getValue();

  // seach for the file in the folder and get it if it exists
  var file = get_file_from_folder(folder, name);  
  
  // if file exists: copy current sheet to this file
  // and rename copied sheet
  if (file != null) {
    var dest_ss = SpreadsheetApp.openById(file.getId());
    cur_ss.getActiveSheet().copyTo(dest_ss).setName(date);
  }

  // if file doesn't exists: make а copy of current spreadsheet,
  // and rename the sheet with the same name as your active sheet
  else {
    var file = DriveApp.getFileById(cur_ss.getId()).makeCopy(folder).setName(name);
    var dest_ss = SpreadsheetApp.openById(file.getId());
    dest_ss.getSheetByName(cur_ss.getActiveSheet().getName()).setName(date);
 
  }
}


// functions searches for the file with given name in given folder and returns the file.
// In case the folder has no such file the function returns 'null'

function get_file_from_folder(folder, name) {
  var files = folder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    if (file.getName() ==  name) return file;
  }
  return null;
}

Keep in mind, sometimes the destination spreadsheet can have a sheet with the same name already. It will get an error. Probably you need to handle that collision somehow.

Upvotes: 1

Related Questions