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

Viewed 40

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());
}
1 Answers

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

Related