表格API匯入表格與格式化

使用此處的腳本，我可以將作業表匯入另一個檔案，但是如果我從 srcRange 中洗掉列，它將不起作用。我的目標是將整個表格復制到另一張表格上，列數偶爾會發生變化；

這有效，但必須指定列：

  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);

這不起作用：

  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);

嘗試了多種變體無濟于事。繼續獲取 > 例外：未找到范圍。

uj5u.com熱心網友回復：

如果你想實作The source range only works right now if you put in the columns but I want to copy the format for the entire sheet.而你的實際錯誤是No error > const srcRange = "Database!A:IX";, error happens if I use > const srcRange = "Database";，下面的修改怎么樣？

從：

const srcRange = "Database!A:IX";

至：

const srcRange = "Database";

和，

從：

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

至：

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

或者

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

添加：

從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.，當我看到你的電子表格時，我明白這個問題的原因是由于plain text格式。當plain text使用 getNumberFormats() 檢索單元格時，, it seems that 回傳 null`。這樣，號碼不顯示。為了反映這個問題，請修改如下。

修改后的腳本：

請為您的測驗情況設定電子表格 ID 和作業表名稱。

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);
}

標籤：谷歌应用脚​​本谷歌表格谷歌表格 API

上一篇：谷歌表格腳本-將公式復制到基于列的值

下一篇：在更改選擇選項之前，文本輸入值不會在頁面加載時顯示