Copy data from specific cell into different sheet with different formatting in a different folder

0
votes

From inside one google sheet, I want to run a script (via script-attached button) that copies another,different, sheet into another, destination folder while naming the new file according to a cell value in the first sheet. Can anyone point me in the right direction? Here's what i have so far:

  function onOpen(e) {                   
  var jobTitle = SpreadsheetApp.getActiveSpreadsheet().getName();
  SpreadsheetApp.getActiveSpreadsheet().getRange("B3").setValue(jobTitle);
   }
  function CreateManFile8() {           
  var copyMan = DriveApp.getFileById("Source ID");                                  
  var toFolder = DriveApp.getFolderById("Destination ID");                                                   
  copyMan.makeCopy(toFolder);
   }

The first function is fine as it allows me to copy the first spreadsheet template, name it and then, onOpen, the file name goes into an appropriate cell of the input sheet from which it populates throughout the spreadsheet via standard sheets reference formulas and such. The second function just copies the "Man." template (from inside the first "Sales" template) and puts it into the appropriate folder without changing the name.

I need a way to name the Man. template according to the value in B3 (or any specified cell) from inside the Sales SpreadSheet template. I have the script attached to a button inside the Sales template but it just copies the second template and names it "Source ID";not the value of B3.

ISSUE BACKGROUND: (FOR THOSE INTERESTED)

Each of the projects I manage at work entail Sales processes contained in a "Sales Spreadsheet" Template composed of several inter-linked sheets and Management processes contained in a "Management Spreadsheet" Template composed of several other inter-linked (with standard formulas) sheets. Eventually, both of these Spreadsheet Templates are copied and used for every new project.

First, each Sales prospect engages sales processes that cause the Sales spreadsheet template to be copied, renamed and iteratively populated with relevant customer and sales data throughout the sales process. Then, at some point, contracts are signed which engage management processes and, consequently, the "Management Spreadsheet" Template is copied, named, and utilized for the project as well.

The idea is to have the "Man. Template" get copied from within the 'Sales Template" after the sales template has been manually named and populated with information collected during the sales process.

Most of the data collected, analyzed, and reported during the management phase of any project is different from the data needed to negotiate the sales processes but, the little bit that is redundant needs to be Programmatically transposed from the Sales Template to the Management Template for each project.

And So, I come to YOU...the stack overflow community for guidance. Any and all serious, service-oriented people offering help will be truly appreciated.

I know most of you are smarter than me. It does not help to be reminded. So, anyone Who actually wants to help a dude out just because they love coding and want to share it, I cant wait to hear from you.

2
google-apps-script
Welcome to StackOverflow. Do you have a question? – Tedinoz
Take a range array and setvalues to any sheet in your spread sheet by that array, please attempt to create your script and inform us the problem. – user11982798
man, you were quick! I will o – Irk
I will post what i have so far so you can take me seriously but no one, anywhere addresses the question. Im trying to articulate that I want to write a function that copies a different spreadsheet to another specified folder but names it according to a value in the first. The spreadsheets are different formats and I just want to know how to take values from inside the source and put them inside specific cells in another spreadsheet template. – Irk
Your question seems to be a duplicate of How do I make copy of spreadsheet and save it to particular folder?. – Tedinoz

2 Answers

0
votes

This just a sample, you must modify it for your purpose, this will copy a file from any folder to other folder with new name:

function CreateFile8() {
  //This assumes the folders are unique, so I don't use ID
  var folders=DriveApp.getFoldersByName('Sistem Folder'); //myfolder = 'my drive' > 'sistem folder'

  if (folders.hasNext())
  {
    var mySrcFolder = folders.next();
    var Destfolders = mySrcFolder.getFoldersByName('Test'); //myDestfolder = 'my drive' > 'sistem folder' > 'Test'
    var files=mySrcFolder.getFilesByName('Jojo'); //file jojo will be copied (only if not exist), and Jojo is as template
    if (files.hasNext() && Destfolders.hasNext())
    {
      var DestFolder = Destfolders.next();
      var myFile = files.next();
      var Destfiles=DestFolder.getFilesByName('Jobe'); //file jojo will be copied (only if not exist in dest), and Jojo is as template
      if (Destfiles.hasNext()==false)
      myFile.makeCopy('Jobe', DestFolder); //And jobe is extracted from your sheet, I don't adopt here to extract from your sheet
    }
  }  
}
0
votes

If I understood correctly, you want a function that makes a copy of the spreadsheet in an specified folder and with the value of the B3 cell as name.

You can try this modification of your function:

  function CreateManFile8() {           
     var copyMan = DriveApp.getFileById("spreadsheet Id").getId();    
     var toFolder = DriveApp.getFolderById("folder Id");       

     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     var sheetName = sheet.getRange('B3').getDisplayValue();

     DriveApp.getFileById(copyMan).makeCopy(sheetName, toFolder);  

}

You can check the documentation of makeCopy and getDisplayValue for more information.