Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to duplicate google sheet as values and formatting only & rename it to a certain cell

I’ve entered the code below on appscript:

function so5691088602(){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetname = "Sheet1";
var basesheet = ss.getSheetByName(sheetname);
var newSheetName = new Date(basesheet.getRange("B3").getValue());
var NumDups = 30;
for (var i=0;i<NumDups;i++){
basesheet.activate;
var tempDate = new Date();
var printdate01 = Utilities.formatDate(new 
Date(tempDate.setDate(newSheetName.getDate()+1+i)), "GMT+10", "MMMM dd, 
yyyy");
// Logger.log("DEBUG: Sheet Date = "+printdate01);
ss.insertSheet(printdate01, {template: basesheet});
}
}

It works perfectly by duplicating the active sheet but how do I modify it so it can print the value and formats of the
sheet only and not the formulas aswell?

Kind Regards, Richard

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

Use dataRange.setValues(dataRange.getValues()), like this:

function so5691088602() {
  const ss = SpreadsheetApp.getActive();
  const templateSheet = ss.getSheetByName('Sheet1');
  const dateStart = templateSheet.getRange('B3').getValue();
  const numDups = 30;
  const timezone = ss.getSpreadsheetTimeZone();
  for (let i = 1; i <= numDups; i++) {
    const tempDate = dateOffset_(dateStart, i);
    const sheetName = Utilities.formatDate(tempDate, timezone, 'MMMM dd, yyyy');
    const newSheet = ss.insertSheet(sheetName, { template: templateSheet });
    const dataRange = newSheet.getDataRange();
    dataRange.setValues(dataRange.getValues());
  }
}


/**
* Gets a new Date object that is offset by numDays from date.
*
* @param {Date} date The date from which to count.
* @param {Number} numDays The number of days to add or subtract from date.
* @return {Date} A new Date object that is offset by numDays from date.
*/
function dateOffset_(date, numDays) {
  const newDate = new Date(date);
  newDate.setDate(date.getDate() + numDays);
  return newDate;
}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading