0

I have a booking form, shown.
I also have a template form, hidden.

This script is assigned to a button to create completed bookings by copying the booking form data to a copy of the template and again hides the template and clears the booking form.

The name of the newly created completed booking sheet is pulled from cell C7.

I need to write an argument to assign a default name if the cell value is null.

function EXPORT() {


var ss = SpreadsheetApp.getActiveSpreadsheet();

var sti = ss.getSheetByName('Template');

var sta = ss.getSheetByName('Booking Confirmation');

var ti = sta.getRange('C7').getValue();

sti.showSheet();

sti.copyTo(ss).setName(ti);

sti.hideSheet();



var stc = ss.getSheetByName(ti);

var source = sta.getRange('A5:C23');

var dest = stc.getRange('A5:C23');

source.copyTo (dest, {contentsOnly: true});



sta.getRange('C5:C5').clearContent();

sta.getRange('C7:C10').clearContent();

sta.getRange('C12:C19').clearContent();

sta.getRange('C21:C23').clearContent();


}​
2

You can use a default value with this pattern:

var ti = sta.getRange('C7').getDisplayValue() || 'default value';

Your function will error out in the event a sheet name is already in use. To guard against that, use a try-catch block, like this:

function exportSheet() {
  const ss = SpreadsheetApp.getActive();
  const templateSheet = ss.getSheetByName('Template');
  const bookingSheet = ss.getSheetByName('Booking Confirmation');
  const targetSheetName = bookingSheet.getRange('C7').getDisplayValue() || 'Booking';
  const targetSheet = templateSheet.copyTo(ss);
  try {
    targetSheet.setName(targetSheetName);
  } catch (error) {
    const timestampString = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), ' yyyy-MM-dd HH:mm:ss');
    targetSheet.setName(targetSheetName + timestampString);
  }
  const sourceData = bookingSheet.getRange('A5:C23');
  const targetRange = targetSheet.getRange('A5');
  sourceData.copyTo(targetRange, { contentsOnly: true });
  bookingSheet
    .getRangeList(['C5', 'C7:C10', 'C12:C19', 'C21:C23'])
    .clearContent();
}
1

Replace

var ti = sta.getRange('C7').getValue();

by

var ti = (() => {
  let value = sta.getRange('C7').getValue();
  return value === '' ? 'add here the default value' : value;
})();

The above uses IIEF, an arrow function expression and the conditional (ternary) operator.

1
  • 1
    Why not use the simpler var ti = sta.getRange('C7').getDisplayValue() || 'default value';? Note that neither the IIEF pattern nor the simpler pattern guard against duplicate sheet names. Oct 22 '21 at 5:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.