Using the script here I am able to import the sheet to another document however it does not work if I remove the columns from the srcRange. My goal is to copy the entire sheets formating over to the other sheet and the number of columns occasionally changes;

This works but the columns have to be specified:

  const srcSpreadsheetId = "1mVlva8Dyxxxxxxxxxxxxxx"; // Please set source Spreadsheet ID.
  const dstSpreadsheetId = "1a2Eb7fQOxxxxxxxxxxxxxx"; // Please set destination Spreadsheet ID.
  const srcRange = "Database!A:I";
  const dstRange = "Database";

  // Here, the date object is retrieved as the serial number.
  const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcRange, { dateTimeRenderOption: "SERIAL_NUMBER", valueRenderOption: "UNFORMATTED_VALUE" }).values;

  const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstRange);
  const sheetId = dstSheet.getSheetId();
  Sheets.Spreadsheets.batchUpdate({ requests: [{ repeatCell: { range: { sheetId }, fields: "userEnteredValue" } }] }, dstSpreadsheetId);
  Sheets.Spreadsheets.Values.update({ values }, dstSpreadsheetId, dstRange, { valueInputOption: "USER_ENTERED" });

  // Here, the number format is copied.
  const numberFormats = SpreadsheetApp.openById(srcSpreadsheetId).getRange(srcRange).getNumberFormats();
  dstSheet.getRange(1, 1, numberFormats.length, numberFormats[0].length).setNumberFormats(numberFormats);

This does not work:

  const srcSpreadsheetId = "1mVlva8Dyxxxxxxxxxxxxxx"; // Please set source Spreadsheet ID.
  const dstSpreadsheetId = "1a2Eb7fQOxxxxxxxxxxxxxx"; // Please set destination Spreadsheet ID.
  const srcRange = "Database";  // <<<<<<<<<<<<<<<<<   Columns not specified
  const dstRange = "Database";

  // Here, the date object is retrieved as the serial number.
  const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcRange, { dateTimeRenderOption: "SERIAL_NUMBER", valueRenderOption: "UNFORMATTED_VALUE" }).values;

  const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstRange);
  const sheetId = dstSheet.getSheetId();
  Sheets.Spreadsheets.batchUpdate({ requests: [{ repeatCell: { range: { sheetId }, fields: "userEnteredValue" } }] }, dstSpreadsheetId);
  Sheets.Spreadsheets.Values.update({ values }, dstSpreadsheetId, dstRange, { valueInputOption: "USER_ENTERED" });

  // Here, the number format is copied.
  const numberFormats = SpreadsheetApp.openById(srcSpreadsheetId).getRange(srcRange).getNumberFormats();
  dstSheet.getRange(1, 1, numberFormats.length, numberFormats[0].length).setNumberFormats(numberFormats);

Have tried multiple variations to no avail. Keep getting > Exception: Range not found.

CodePudding user response：

If you want to achieve The source range only works right now if you put in the columns but I want to copy the format for the entire sheet. and your actual error is No error > const srcRange = "Database!A:IX";, error happens if I use > const srcRange = "Database";, how about the following modification?

From:

const srcRange = "Database!A:IX";

To:

const srcRange = "Database";

And,

From:

const numberFormats = SpreadsheetApp.openById(srcSpreadsheetId).getRange(srcRange).getNumberFormats();

To:

const numberFormats = SpreadsheetApp.openById(srcSpreadsheetId).getSheetByName(srcRange).getDataRange().getNumberFormats();

or

const srcSheet = SpreadsheetApp.openById(srcSpreadsheetId).getSheetByName(srcRange);
const numberFormats = srcSheet.getRange(1, 1, srcSheet.getMaxRows(), srcSheet.getMaxColumns()).getNumberFormats();

Added:

From I tested both the first and second solution you suggested with a smaller amount of data and both work. The only wierd part is that for numbers stored as text they are in a white font and you cannot change the color., when I saw your Spreadsheet, I understood that the reason for this issue is due to plain text format. When the cells of plain text are retrieved with getNumberFormats(), it seems that null` is returned. By this, the number is not displayed. In order to reflect this issue, please modify it as follows.

Modified script:

Please set the Spreadsheet IDs and sheet names for your test situation.

function sample() {
  const srcSpreadsheetId = "1mVlva8Dyxxxxxxxxxxxxxx"; // Please set source Spreadsheet ID.
  const dstSpreadsheetId = "1a2Eb7fQOxxxxxxxxxxxxxx"; // Please set destination Spreadsheet ID.
  const srcRange = "Database";  // <<<<<<<<<<<<<<<<<   Columns not specified
  const dstRange = "Database";

  // Here, the date object is retrieved as the serial number.
  const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcRange, { dateTimeRenderOption: "SERIAL_NUMBER", valueRenderOption: "UNFORMATTED_VALUE" }).values;

  const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstRange);
  const sheetId = dstSheet.getSheetId();
  Sheets.Spreadsheets.batchUpdate({ requests: [{ repeatCell: { range: { sheetId }, fields: "userEnteredValue" } }] }, dstSpreadsheetId);
  Sheets.Spreadsheets.Values.update({ values }, dstSpreadsheetId, dstRange, { valueInputOption: "USER_ENTERED" });

  // Here, the number format is copied.
  const srcSheet = SpreadsheetApp.openById(srcSpreadsheetId).getSheetByName(srcRange);
  const range = srcSheet.getRange(1, 1, srcSheet.getMaxRows(), srcSheet.getMaxColumns());
  const numberFormats = range.getNumberFormats().map(r => r.map(c => c || "@"));
  const styles = range.getTextStyles();
  dstSheet.getRange(1, 1, numberFormats.length, numberFormats[0].length).setNumberFormats(numberFormats).setTextStyles(styles);
}