index current column in a variable for java script code for a google sheet project

user_14975873 0
Viewed 73

I've just started learning javascript and I'm trying to do something like this:

I've got 2 different sheets, Sheet1 has some data while Sheet2 is empty. Sheet1 has numbers in column A and some stuff on other columns.

I was trying to make a script that runs when I imput an integer number on column A of Sheet2 (that had to be unique in that sheet and present on column A of Sheet1) and if it's equal to a number of column A of Sheet1 than the script has to copy the corrisponding row of Sheet1 to Sheet2.

What I achieved to do is something like this:

function votazioni() {
  var spreadsheet = SpreadsheetApp.getActive();
  var activeSheet= spreadsheet.getActiveSheet();
  var baseDati= spreadsheet.getSheetByName("Foglio2");
  var cellRange = activeSheet.getActiveCell();
  var imput= activeSheet.getActiveCell().getValue();
  var indiceImput= cellRange.getColumn();
  var copiaDati=baseDati.getActiveRange(1,indiceImput).getValue();
        for (var i=1; i<1204; i++){
          if (imput == copiaDati) {
            for( var j=2; j<7; j++){
              var copiaValore=baseDati.getRange (j,indiceImput).getValue();
              activeSheet.getRange(j,indiceImput).setValue(copiaValore);
            }
            i=1204;          
          }    
        }
}

For example if i type 45 on A2 of Sheet2 than B2 should be Paperino, C2 should be 3, D2 should be agafdh

Sorry for bad english and thank you in advance.

google-apps-script google-sheets javascript
1 Answers

I believe your goal as follows.

  • When 45 is put to the cell "A2" of "Sheet2", you want to search the value of 45 from the column "A" of "Sheet1" and copy the searched row to "Sheet2".
  • You want to achieve this using Google Apps Script.

Modification points:

  • In your script, imput and copiaDati are compared. But it seems that the column "A" of "Sheet1" is not scanned.
  • In this case, I thought that OnEdit trigger might be suitable for achieving your goal. When OnEdit trigger is used, when the column "A" of "Sheet2" is edited, the script can be automatically run. I thought that this situation might be useful for your situation.

Above points are reflected to a script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet. And, please edit the column "A" of "Sheet2". By this, the script is run, and the put value is searched from the column "A" of "Sheet1" and copy the row to "Sheet2".

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet2" || range.getColumn() != 1 || range.getRow() == 1) return;
  const value = range.getValue();
  const srcSheet = e.source.getSheetByName("Sheet1");
  const srcRange = srcSheet.getRange("A2:A" + srcSheet.getLastRow()).createTextFinder(value).matchEntireCell(true).findNext();
  if (srcRange) {
    srcRange.offset(0, 1, 1, srcSheet.getLastColumn() - 1).copyTo(range.offset(0, 1));
  }
}

  • In this sample script, from your question, the sheet names of "Sheet1" and "Sheet2" are used. So if your actual situation is different from them, please modify them.

  • The flow of this script is as follows.

    1. Check whether the column "A" of "Sheet2" is edited.
    2. When the column "A" of "Sheet2" is edited, retrieve the edited value.
    3. Search the edited value from the column "A" of "Sheet1". In this case, TextFinder is used.
    4. Copy the row from "Sheet1" to "Sheet2".

Note:

  • In this script, in order to achieve your goal, the OnEdit trigger of simple trigger is used. So when you directly run the function of onEdit at the script editor, an error occurs. Please be careful this. When you run the script, please edit the column "A" of "Sheet2".

  • When you want to manually run the script to the active cell, you can also use the following script. In this case, you can run the script at the script editor.

      function myFunction() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getActiveSheet();
    const range = sheet.getActiveCell();
    if (sheet.getSheetName() != "Sheet2" || range.getColumn() != 1 || range.getRow() == 1) return;
    const value = range.getValue();
    const srcSheet = ss.getSheetByName("Sheet1");
    const srcRange = srcSheet.getRange("A2:A" + srcSheet.getLastRow()).createTextFinder(value).matchEntireCell(true).findNext();
    if (srcRange) {
      srcRange.offset(0, 1, 1, srcSheet.getLastColumn() - 1).copyTo(range.offset(0, 1));
    }
  }

References:

Added:

About your additional question as follows.

it is possible to avoid to be double values on column A of the Sheet2?

The sample script is as follows.

Sample script:

In this case, onEdit is mofidied. When you use this script, please put a value to the column "A" of "Sheet2". In this case, when the put value is existing in the column "A" of "Sheet2", the put value is removed. On the other hand, when the put value is NOT existing in the column "A" of "Sheet2", the put value is searched from the column "A" of "Sheet1" and copy the row.

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet2" || range.getColumn() != 1 || range.getRow() == 1) return;
  const value = range.getValue();
  const values = sheet.getRange("A2:A" + sheet.getLastRow()).getValues();
  values.splice(range.getRow() - 2, 1);
  if (values.flat().includes(value)) {
    range.clearContent();
    return;
  }
  const srcSheet = e.source.getSheetByName("Sheet1");
  const srcRange = srcSheet.getRange("A2:A" + srcSheet.getLastRow()).createTextFinder(value).matchEntireCell(true).findNext();
  if (srcRange) {
    srcRange.offset(0, 1, 1, srcSheet.getLastColumn() - 1).copyTo(range.offset(0, 1));
  }
}
  • When you don't want to remove the put value on "Sheet2" when the value is existing, please remove range.clearContent();.
user_71086530
Related