getsheetbyname with variable cell-CodePudding

I have a sheet called Audit with cell B1 corresponding to a specific shopID.

I have built a sheet with each shopID called ShopID PrevAudit (555PrevAudit)

When they complete the audit they click a button that should copy values and format to the corresponding sheet i've created but I'm getting hung up on calling the correct sheet based on a B1 entry.

This is what I had been using but it was static and i want to make this easier on myself. I'm not sure why this is so difficult for me. Thanks for any help you can provide.

function copyaudit() {
    var source = SpreadsheetApp.openById('SheetID');
    var sourceSheet = source.getSheetByName('Audit');
    var sourceRange = sourceSheet.getDataRange();
    var sourceValues = sourceRange.getValues();

    var tempSheet = source.getSheetByName('555PrevAudit');
    var tempRange = tempSheet.getRange('A1:L51');

    var destination = SpreadsheetApp.openById('SheetID');

    sourceRange.copyTo(tempRange);  // paste all formats?, broken references
    tempRange.offset(0, 0, sourceValues.length, sourceValues[0].length)
    .setValues(sourceValues);  // paste all values (over broken refs)
}

CodePudding user response：

Using a JS template string, you can generate the sheet name:

`${destID}PrevAudit`

To put it in context:

function copyaudit() 
{
  const range = 'A1:L51';
  
  let spreadSheet = SpreadsheetApp.openById('SheetID');
  let sourceSheet = spreadSheet.getSheetByName('Audit');
  let sourceRange = sourceSheet.getRange(range);

  // Get B1
  let destID = sourceRange.getValues()[0][1];
  let destSheet = spreadSheet.getSheetByName(`${destID}PrevAudit`);

  sourceRange.copyTo(destSheet.getRange(range));
}