CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Javier Alonso Delgado
Javier Alonso Delgado

Reputation: 321

How to copy values from one sheet and paste them into another using Google Sheets Macros?

I'm writing a Google Sheets Macros without having a lot of knowledge about syntax.

What I want to do is the following:

I want to copy the values which are matching in a source matrix into another table. However, I don't know how to write that as a Macros.

I've written the following code:

function CalcularCruces() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = spreadsheet.getSheetByName("Cruces Activo-Amenazas");
  var destinationSheet = spreadsheet.getSheetByName("Análisis de Riesgos");

  /** Total number of left column values from source table **/
  const maxAmenazas = 29;

  for(var i = 0; i < maxAmenazas; i++) {
    /** Now I need to get the column and row values which are matching with the checkbox 
        and paste them into another table **/
  }

};

Here is an example of the input table and how the output table should look like after executing the macros.

Input Table Sheet Output Table Sheet

Edit:

I need the data to be written next to this static columns:

Actual Output Actual Output

Desired Output Desired Output

Upvotes: 4

Views: 807

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

You can do the following:

  • Retrieve the data from the source sheet via getDataRange and getValues.
  • For each row in this data (excluding the headers row, that has been retrieved and removed from the array with shift), check which columns have the checkbox marked.
  • If the corresponding checkbox is marked, write the corresponding values to the destination sheet with setValues.

It could be something like this: 

function CalcularCruces() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = spreadsheet.getSheetByName("Cruces Activo-Amenazas");
  var destinationSheet = spreadsheet.getSheetByName("Análisis de Riesgos");
  destinationSheet.getRange("A2:B").clearContent();
  var values = sourceSheet.getDataRange().getValues(); // 2D array with all data from source sheet
  var headers = values.shift(); // Remove and retrieve the headers row
  for (var i = 1; i < values[0].length; i++) { // Iterate through each column
    for (var j = 0; j < values.length; j++) { // Iterate through each row
      var activo = values[j][0]; // Activo corresponding to this row
      if (values[j][i]) { // Check that checkbox is marked
        // Get the row index to write to (first row in which column A and B are empty):
        var firstRow = 2;
        var firstCol = 1;
        var numRows = destinationSheet.getLastRow() - firstRow + 1;
        var numCols = 2;
        var firstEmptyRow = destinationSheet.getRange(firstRow, firstCol, numRows, numCols).getValues().filter(function(row) {
          return row[0] !== "" && row[1] !== "";
        }).length + firstRow;
        // Write data to first row with empty columns A/B:
        destinationSheet.getRange(firstEmptyRow, firstCol, 1, numCols).setValues([[headers[i], activo]]);
      }
    }
  }
};

Notes:

  • All data is added to the target sheet every time the script is run, and this can lead to duplicate rows. If you want to avoid that, you can use clearContent at the beginning of your script, after declaring destinationSheet, to remove all previous content (headers excluded):
destinationSheet.getRange("A2:B").clearContent();
  • In this sample, the number of amenazas is not hard-coded, but it dynamically gets the number of rows in the source sheet with getValues().length. I'm assuming that's a good outcome for you.
  • UPDATE: Since you have other columns in your target sheet, you cannot use appendRow but setValues. First, you have to find the index of the first row in which columns A and B are empty. This is achieved with filtering the array of values in columns A-B and filtering out the elements in which the two values are empty (with filter).

Reference:

Upvotes: 1

Related Questions