GoogleSheets CopyPaste Column's formatting and formula to newly created Column-CodePudding

I have a Google Sheet with a sheet [Summary] with a button that when clicked, performs a macro to add a new column to a different sheet, [May 2022], and then, take a value from the button's original sheet, add that value to the _1 cell of that new column, and also paste in some values from the page's original column. It also adds a new row in the base sheet, to log the particular name in place, to create a growing list of collected names & matching dates. My code has a prompt to confirm this with a Yes/No.

I'd like to have this macro copy the entirety of the column's range in the new sheet's (C2:C) range, into the newly added column under the _1 cell, so that both formatting, conditional formatting, and formulas for each cell, are added in the (_2:_) range. The process should be that it creates a new column, adds the value of the name, and underneath, adds all of the formatting and formulae from (C2:C), all into the new columns as they're generated. I do not want values copy/pasted; it needs the formula pasted.

function Add_Patient_Button(){
// Display a dialog box with a message and "Yes" and "No" buttons. The user 
//can also close the
// dialog by clicking the close button in its title bar.
var ui = SpreadsheetApp.getUi();
var response = ui.alert(
 'Please confirm that you are about to add a new patient.', 
 ui.ButtonSet.YES_NO);

 // Process the user's response.
  if (response == ui.Button.YES) {
  Add_New_Patient_Column();
 }
 if (response == ui.Button.NO) {
 }
else {

 }
 } 


function Add_New_Patient_Column()
{
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('9:9').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('A9').activate();
  spreadsheet.getRange('E3:F3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('C9').activate();
  spreadsheet.getCurrentCell().setValue('0');
  var ss = SpreadsheetApp.getActive();
  var date = ss.getSheetByName('Summary').getRange('E3').getDisplayValue();
  var namesSheet = ss.getSheetByName('May 2022')
  var names = namesSheet.getRange('C2:C').getValues().filter(function (r) {return r[0]})
  names.unshift([date])
  namesSheet.getRange(1, namesSheet.getLastColumn()  1, names.length, 1).setValues(names)
  var spreadsheet = SpreadsheetApp.getActive();
}

Here is a copy of my sheet. https://docs.google.com/spreadsheets/d/1GbdoAY24Y1AMuDsXbRdUnDx4RuPzSN4krl-qcaCwC5o/edit?usp=sharing

What do I need to change in my macro to make this happen?

CodePudding user response：

Not sure if I understand the task a whole. I'd propose to clone the last existed column on the sheet 'May 2022' and to change its first cell according the name in cell E3 of the sheet 'Summary'.

It can be done if the function Add_New_Patient_Column() will look as follows:

function Add_New_Patient_Column() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('May 2022');
  var last_col = sheet.getLastColumn();
  var last_row = sheet.getLastRow();

  // add a new empty column
  sheet.insertColumnAfter(last_col);

  // copy all cells from the last column into the new column
  sheet.getRange(1,last_col, last_row, 1).copyTo(sheet.getRange(1,last_col 1));

  // change the header of the new column
  var src_sheet = ss.getSheetByName('Summary');
  var [name, date] = src_sheet.getRange('E3:F3').getValues().flat();
  sheet.getRange(1,last_col 1).setValue(name);

  // append a new row at the 'Summary' sheet
  var new_row = [name, date, 0, '???'];
  src_sheet.appendRow(new_row);
}

Update

If you want to add the new row above the row 9 you have to replace the line:

src_sheet.appendRow(new_row);

with:

src_sheet.insertRowBefore(9);
src_sheet.getRange('a9:d9').setValues([new_row]);