Sort of VLookup script deleting all the data in the column (Appscript, Gsheet)

Question

Good afternoon,

I am currently developing a database on Google sheets that I am trying to populate with data from another GSheet using a script that acts sort of like a VLookup

I used the script from this comment : https://stackoverflow.com/a/60266697/19427703

The issue I am facing is that when I run my script, all the data (even where the data is not matching) is erased. I would like my script to leave the data in the cell when the ID is not matching.

I would appreciate any help,

Thank you in advance, here is my code :

const ss = SpreadsheetApp.getActive();
/**
 * @param {GoogleAppsScript.Spreadsheet.Sheet} fromSht -Sheet to import from
 * @param {GoogleAppsScript.Spreadsheet.Sheet} toSht -Sheet to import to
 * @param {Number} fromCompCol -Column number of fromSht to compare
 * @param {Number} toCompCol -Column number of toSht to compare
 * @param {Number} fromCol -Column number of fromSht to get result
 * @param {Number} toCol -Column number of toSht to get result
 */

function Refresh(
  fromSht = ss.getSheetByName('Sheet1'),
  toSht = ss.getSheetByName('Sheet2'),
  fromCompCol = 2,
  toCompCol = 2,
  fromCol = 1,
  toCol = 1
) {
  const toShtLr = toSht.getLastRow();
  const toCompArr = toSht.getRange(2, toCompCol, toShtLr - 1, 1).getValues();
  const fromArr = fromSht.getDataRange().getValues();
  fromCompCol--;
  fromCol--;

  /*Create a hash object of fromSheet*/
  const obj1 = fromArr.reduce((obj, row) => {
    let el = row[fromCompCol];
    el in obj ? null : (obj[el] = row[fromCol]);
    return obj;
  }, {});

  //Paste to column
  toSht
    .getRange(2, toCol, toShtLr - 1, 1)
    .setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [row[toCol]])));
}

This is my inputs and results (imagine that for the six matrix the range is A1:B4)

Sheet 1 :

Name	ID
New Name 1	101
New Name 5	105
New Name 10	110

Sheet 2 :

Name	ID
Name 1	101
Name 2	102
Name 3	103

Expected result :

Name	ID
New Name 1	101
Name 2	102
Name 3	103

Obtained result with [row] :

Name	ID
New Name 1	101
102	102
103	103

Obtained result with [row[toCol]] :

Name	ID
New Name 1	101
	102
	103

Obtained result with [null] :

Name	ID
New Name 1	101
	102
	103

It looks like you copied this from somewhere else. If you're using someone else's work without giving them credit, that constitutes plagiarism, which is not welcome on Stack Exchange. To fix it, you can [edit], include a [link](/editing-help#links) to the source, mention the author's name, and [quote](/editing-help#simple-blockquotes) the copied content. For more details, see [referencing help](/help/referencing) and [this FAQ](https://meta.stackexchange.com/q/160077/343832). — TheMaster, Jun 27 '22 at 15:12
What exactly is this portion of your code supposed to be doing? It's not obvious by looking at it. `const obj1 = fromArr.reduce((obj, row) => {`. — TheWizEd, Jun 27 '22 at 16:22
@TheWizEd As the comment says, it creates a hash object for 1:1 fast access. See linked question. Basically something like, ``['a','b']`` to `{'a':1, 'b':2}` — TheMaster, Jun 27 '22 at 17:39
Could you [edit] to show your data structure as a screenshot - inputs, expected results and actual results? — TheMaster, Jun 28 '22 at 12:37
I've put a link, I couldn't save an image. Thank you again for the help you are giving me — qtreboux, Jun 28 '22 at 13:31
Link says 403.. Maybe use a [table](https://webapps.stackexchange.com/a/161855/) — TheMaster, Jun 28 '22 at 14:02
@TheMaster Thank you a lot ! It is working perfectly as intended !! You are truly the master of Appscript ;) — qtreboux, Jun 28 '22 at 15:04

TheMaster · Accepted Answer · 2022-06-28T14:50:09.757

null is set here, if row[0] is not in hash object:

setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [null]))); }

Get the column to set as toColRng and get it's values like to set in place of null:

const ss = SpreadsheetApp.getActive();
/**
 * @param {GoogleAppsScript.Spreadsheet.Sheet} fromSht -Sheet to import from
 * @param {GoogleAppsScript.Spreadsheet.Sheet} toSht -Sheet to import to
 * @param {Number} fromCompCol -Column number of fromSht to compare
 * @param {Number} toCompCol -Column number of toSht to compare
 * @param {Number} fromCol -Column number of fromSht to get result
 * @param {Number} toCol -Column number of toSht to get result
 * @author TheMaster https://stackoverflow.com/users/8404453
 */
function vlookup_3(
  fromSht = ss.getSheetByName('Sheet1'),
  toSht = ss.getSheetByName('Sheet2'),
  fromCompCol = 1,
  toCompCol = 1,
  fromCol = 2,
  toCol = 2
) {
  const toShtLr = toSht.getLastRow();
  const toCompArr = toSht.getRange(2, toCompCol, toShtLr - 1, 1).getValues();
  const toColRng = toSht.getRange(2, toCol, toShtLr - 1, 1);
  const toColArr = toColRng.getValues();
  const fromArr = fromSht.getDataRange().getValues();
  fromCompCol--;
  fromCol--;

  /*Create a hash object of fromSheet*/
  const obj = fromArr.reduce((obj, row) => {
    let el = row[fromCompCol];
    el in obj ? null : (obj[el] = row[fromCol]);
    return obj;
  }, {});

  //Paste to column
  toColRng.setValues(
    toCompArr.map((row, i) => (row[0] in obj ? [obj[row[0]]] : toColArr[i]))
  );
}

Hello, Thank you for your answer. When I replace [null] by row, my script now replace my column by the matching argument. I tried to replace [null] by row[toCol] but I always land on an error. Any idea on how to fix this ? — qtreboux, Jun 28 '22 at 06:32
I get hit with this message : " Error Exception: The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 1." — qtreboux, Jun 28 '22 at 07:59
@qtreboux Did you copy it exactly as here? With the brackets? Could you copy paste your setValue line here? — TheMaster, Jun 28 '22 at 08:01
.setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [row[toCol]]))); This is my line, and now it is back at my initial situation deleting all the value in the column when the argument is not matching. Without the bracket it was working just for one column (because i have this code multiple times for different columns) before sending the error message i copied above — qtreboux, Jun 28 '22 at 11:59

Sort of VLookup script deleting all the data in the column (Appscript, Gsheet)

1 Answers1