How To Correct Format Issue Exporting Data From One Google Spreadsheet To Another?-CodePudding

Script Source: Coutesy of AJ Aviles

CodePudding user response：

I believe your goal is as follows.

You want to copy the sheet by fixing the values.

In your situation, how about the following sample script?

Sample script:

Please set the source Spreadsheet ID, source sheet name, destination Spreadsheet ID and destination sheet name.

function sample() {
  const srcSpreadsheetId = "###"; // Please set the source Spreadsheet ID.
  const srcSheetName = "Send Data"; // Please set the source sheet name.
  const dstSpreadsheetId = "###"; // Please set the destination Spreadsheet ID.
  const dstSheetName = "###"; // Please set the destination sheet name.

  // Process at source Spreadsheet.
  const srcSS = SpreadsheetApp.openById(srcSpreadsheetId);
  const srcSheet = srcSS.getSheetByName(srcSheetName);
  const temp1 = srcSheet.copyTo(srcSS);
  temp1.deleteRow(1);
  const range1 = temp1.getDataRange();
  range1.copyTo(range1, { contentsOnly: true });
  range1.clearDataValidations();

  // Process at destination Spreadsheet.
  const dstSS = SpreadsheetApp.openById(dstSpreadsheetId);
  const dstSheet = dstSS.getSheetByName(dstSheetName);
  const temp2 = temp1.copyTo(dstSS);
  const range2 = temp2.getDataRange();
  range2.copyTo(dstSheet.getRange("A1"));

  // Remove template sheets.
  srcSS.deleteSheet(temp1);
  dstSS.deleteSheet(temp2);
}

In your showing script, it seems that the values, the background colors, the font styles, and so on are trying to be copied. In this case, I thought that the script might be a bit complicated. So, in this sample script, the sheet is copied as the fixed values. I thought that by this, the script might be a bit simple.

Sample script:

References: