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 :

Sheet 2 :

Expected result :

Obtained result with [row] :

Obtained result with [row[toCol]] :

Obtained result with [null] :

CodePudding user response：

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