Retrieve values to desired sheet name, even that particulat destination sheet is placed lastly-CodePudding

The below function works fine when the destination sheet is on the first sheet, If I moved this UserForm sheet to the second, or fourth sheet, it gives an error

API call to sheets.spreadsheets.values.batchUpdate failed with error: Invalid data[9]: Unable to parse range:

Please help me to fix :)

function SearchUF() {
  const srcSpreadsheetId = "My google sheet ID"; // example ID.
  const srcSheetName = "INVLOG";
  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("L2").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('User Form Number Not Found');
  }

   // Retrieve the values from the searched row.
 const r = srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn()).getA1Notation();
const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, `'${srcSheetName}'!${r}`, {valueRenderOption: "FORMULA"}).values[0];

  

  // Put the values to the cells of "USERFORM" sheet.
  const expandRangeList = ["J4", "G4", "C3","C6" ];

  const data = expandRangeList.map((range, i) => ({ range, values: [[values[i] || ""]] }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, dstSpreadsheet.getId());
}

CodePudding user response：

From The below function works fine when the destination sheet is on the first sheet, If I moved this UserForm sheet to the second, or fourth sheet, it gives an error and your showing script, I thought that the reason of your issue might be due to that data of const data = expandRangeList.map((range, i) => ({ range, values: [[values[i] || ""]] })); has no sheet names. If my understanding is correct, how about the following modification?

From:

const data = expandRangeList.map((range, i) => ({ range, values: [[values[i] || ""]] }));

To:

const data = expandRangeList.map((range, i) => ({ range: `'${dstSheetName}'!${range}`, values: [[values[i] || ""]] }));

By this modification, the sheet name of the destination sheet is added to the range of A1Notation.

Reference:

-Method: spreadsheets.values.batchUpdate