我有一個腳本，可以幫助搜索資料表中的資料并將其復制到用戶表單表到相關單元格。現在它只得到值而不是公式，我需要將公式和值都復制到目標單元格。
function searchdata() {
const srcSpreadsheetId = "1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA"; // Please set the source Spreadsheet ID (WB-DataSheet).
const srcSheetName = "DataSheet";
const dstSheetName = "UserForm";
// Retrieve values from source sheet and create an array and search value.
const dstSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const dstSheet = dstSpreadsheet.getSheetByName(dstSheetName);
const search = dstSheet.getRange("B1").getValue();
// Search the value.
const srcSpreadsheet = SpreadsheetApp.openById(srcSpreadsheetId);
const srcSheet = srcSpreadsheet.getSheetByName(srcSheetName);
const range = srcSheet.getRange("A2:A" srcSheet.getLastRow()).createTextFinder(search).findNext();
if (!range) {
SpreadsheetApp.getUi().alert('UserForm Number Not Found');
}
// Retrieve the values from the searched row.
const values = srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn()).getValues()[0];
// Put the values to the cells of "UserForm" sheet.
const expandRangeList = ["C3", "C7", "C8", "D8", "D6", "D4", "E8", "E19", "E20", "E21", "E22", "B10", "C10", "D10", "E10", "B11", "C11", "D11", "E11", "B12", "C12", "D12", "E12", "B13", "C13", "D13", "E13", "B14", "C14", "D14", "E14", "B15", "C15", "D15", "E15", "B16", "C16", "D16", "E16", "B17", "C17", "D17", "E17", "B18", "C18", "D18", "E18", "B19", "C19", "B20", "C20", "B21", "C21", "B22", "C22","D19", "D20", "D21", "D22"];
const data = expandRangeList.map((range, i) => ({ range, values: [[values[i] || ""]] }));
Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, dstSpreadsheet.getId());
}
還共享了兩個包含實際資料的電子表格（用戶表單和資料表）供您參考https://docs.google.com/spreadsheets/d/1NY_ckzEWxU7DCGro5tTqzpiOi6iG5PAQFxpZg0OKodY/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA/edit?usp=sharing
uj5u.com熱心網友回復：
在這種情況下，如何進行以下修改？
從：
const values = srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn()).getValues()[0];
到：
const r = srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn()).getA1Notation();
const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, `'${srcSheetName}'!${r}`, {valueRenderOption: "FORMULA"}).values[0];
- 在這種情況下，這些值是通過
srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn())使用帶有valueRenderOption: "FORMULA". 這樣，可以檢索值和公式。
參考：
- 方法：電子表格.values.get
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/441620.html
標籤：谷歌应用脚本