Question: For different analysis, I use different samples, but I need to make it transparent how the sample came about. Stata shows me *"XX observations dropped"* after each drop command. Is there a way to get R to state the number of observations dropped in the console during a "tidyverse styled" sample selection (see below)? In this example I would like to see in the console how many observations were dropped with the filter command and the drop\_na command. I tried `summarise_all(~sum(is.na(.)))` but it was unsuccessful.

```
capmkt_df <- stata_df %>%
  filter(change != 1 & reg_mkt == 1) %>% 
  select(any_of(capmkt_vars)) %>%
  mutate_at(vars(country, year), factor) %>%
  drop_na()

```

Answer: 
**Description**

Although I'm not able to test it, and I hope I didn't make any typos, I believe this sample script will copy both values and formulas to the destination range. `getFormulas()` returns an array of strings representing the formulas in each cell. These strings can simply be copied to the values array and then the values array can be put back using `setValues()`.

It is customary to start variable names with a lowercase letter. Upper case is usually used for Class or Global variables.

All of the sheet activation, spreadsheet activation serve no purpose that I can see. You simply need the source spreadsheet `ss2` and the destination spreadsheet `ss1`.

Once you `let newClient = SpreadsheetApp.getActive().getSheetByName("NewClient");` the variable `newClient` retains a reference to that instance of the sheet named "NewClient". You don't need to activate it to use it or any of its properties or methods.

**Script**

```
function myTest() {
  try {
    let ss1 = SpreadsheetApp.getActiveSpreadsheet();
    let newClient = ss1.getSheetByName("NewClient");
    let url = newClient.getRange('B106').getValue();
    let ss2 = SpreadsheetApp.openByUrl(url);
    // Get quotes from ss2
    let quotes = ss2.getSheetByName("Quotes");
    let lastRow = quotes.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    let source = quotes.getRange("f2:f"+lastRow);
    let values = source.getValues();
    let formulas = source.getFormulas();
    // Get destination range in ss1
    quotes = ss1.getSheetByName('Quotes');
    let dest = quotes.getRange("f2:f"+lastRow);
    for( let i=0; i<lastRow-1; i++ ) {  // Started in row 2
      if( formulas[i][0] !== "" ) {
        values[i][0] = formulas[i][0];
      }
    }
    dest.setValues(values);
  }
  catch(err) {
    console.log(err);
  }
}

```
Try to change the loop:

```
for (var i = 0; i < lastRow; i++) {
  if(dest.setFormulas[Formulas[i]] = ""){
    dest.setValues(Values[i][0]);
    dest.setFormulas(Formulas[i][0]);
  }
}

```

This way:

```
for (var i = 0; i < lastRow; i++) {
  if (Formulas[i][0] != "") {
    dest.setFormula(Formulas[i][0]);
  } else {
    dest.setValue(Values[i][0]);
  }
}

```

**Update**

Based on your spreadsheets the a final code should look like this:

```
function test() {

  var dest_ss    = SpreadsheetApp.getActiveSpreadsheet();
  var newClient_sheet = dest_sheet.getSheetByName('ImpNewClient');
  var url        = newClient_sheet.getRange('B106').getValue();

  var source_ss  = SpreadsheetApp.openByUrl(url);
  var src_sheet  = source_ss.getSheetByName('Quotes');
  var lastRow    = src_sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var source     = src_sheet.getRange('f2:f' + lastRow);

  var values     = source.getValues();
  var formulas   = source.getFormulas();

  var dest_sheet = dest_ss.getSheetByName('Quotes');

  for (var i = 0; i < lastRow - 1; i++) {
    var cell = dest_sheet.getRange('f'+ (i + 2));
    if (formulas[i][0] !== '') { 
      cell.setFormula(formulas[i][0])
    }
    else { 
      cell.setValue(values[i][0])
    }
  }

  dest_sheet.activate()
}

```

And it's really confusing when you're using variables like `ss` / `SS`, and `quotes` / `Quotes` simultaneously. I'd advice you to use names like `dest_ss` / `src_ss`, `dest_sheet` / `src_sheet`.

And first letter in upper case (`Values`, `Formulas`, etc) means a class name usually, it makes sense don't use it for variables (`values`, `formulas`, etc).