0

Hello and thank you in advance

I have street names in column D and in column A. I would like if possible that it pastes below column D the data of column A. But just those which are not in column D.

thank you for your time

Claude

Hello and thank you in advance

I know how to do in VBA but I would like in google AppScript to make it accessible.

Temjeh
  • 1
  • 1

1 Answers1

0

I believe your goal is as follows.

  • You want to copy the values from column "A" to column "D" on the same sheet on Google Spreadsheet.
  • When the values are copied, you want to append only the new values to column "D".
  • You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet. And, please set your sheet name, and save the script.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.

  // Ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const values = sheet.getRange("A1:D" + sheet.getLastRow()).getValues();
  const colD = new Map(values.map(([, , , d]) => [d, d]));
  const addValues = values.flatMap(([a]) => (a.toString() == "" || colD.has(a)) ? [] : [[a]]);
  if (addValues.length == 0) return;
  sheet.getRange(sheet.get1stNonEmptyRowFromBottom(4) + 1, 4, addValues.length).setValues(addValues);
}

Testing:

When this script is run, the following result is obtained.

enter image description here

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • thank you very much for your help It's the perfection! Enjoy the rest of the evening – Temjeh Jul 05 '23 at 01:55
  • @Temjeh About `thank you very much for your help It's the perfection! Enjoy the rest of the evening`, welcome. Thank you for letting me know. I'm glad your issue was resolved. If your question was solved, please push an accept button. Other people who have the same issue with you can also base your question as a question that can be solved. And I think that your issue and solution will be useful for them. If you don't find the button, feel free to tell me. https://stackoverflow.com/help/accepted-answer – Tanaike Jul 05 '23 at 02:00