Google Sheets Macro switch Statements

Question

I have a problem that seems simple but I cannot get it, I need some help.

I am trying to create a macro that takes data from a live Sheet each week and copies it in to a new sheet within the same workbook. The macro will paste the information in a new sheet in the workbook to what ever is relevant to that week.

I can get the information to copy and paste but I cannot get it to copy and past in respect to the week number.

The week number value is driven by a "=Month()" function from a live time and date driven function "=Now()"

If I erase the Case statements, I can get the value of the week "source" to display on a sheet. However I cannot seem to get the macro to understand the value and then select the respected Case statement.

Currently I have taken out the =Month() function and typed in 32 for the week. It still doesnt seem to be selecting the value. --Original Code--

function copyValuesOnly2(copyFromRange, copyToRangeStart) {
    var currentWeek = 'Live Summary!B9';

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var source = ss.getRange('Live Summary!B9');
    var copyFromRange = source ;
    var Value = ss.getRange('Live Summary!A1:I8')

switch (source){
  case 32:
      var copyToRangeStart = 'Week 32!A1:I8';
      Value.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
break;
  case 33:
      var copyToRangeStart = 'Week 33!A1:I8';
      Value.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
   break;
 }

}

In your code, the source variable is a range object; you need to get the value from that range: var source = ss.getRange("Live Summary!B9").getValue();. Also, you refer to the MONTH() spreadsheet function to retrieve the week number - do you not require the WEEKNUM() function instead? — AdamL
AdamL - Thank you! Yes! this is exactly what I needed to get the value! aww pal you dont know how much that has put me at rest! — Rory O'Connor

Unknown Unknown · Accepted Answer · 2016-03-13T15:26:03

In your code, the source variable is a range object; you need to get the value from that range:

var source = ss.getRange("Live Summary!B9").getValue();

Also, you refer to the MONTH() spreadsheet function to retrieve the week number - do you not require the WEEKNUM() function instead? As per AdamL's comment

OPs Code:

function copyValuesOnly2(copyFromRange, copyToRangeStart) {
var currentWeek = "Live Summary!B9";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange("Live Summary!B9").getValue();
  var copyToRangeStart = "";
  var copyFromRange = source ;
  var Value = ss.getRange('Live Summary!A1:I8')

switch (source){
    case 32:
      var copyToRangeStart = 'Week 32!A1';
      Value.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true}); 
      Logger.log(copyToRangeStart);

  break;
    case 33:
      var copyToRangeStart = 'Week 33!A1:I8';
      Value.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
  break;

    case 34:
       var copyToRangeStart = 'Week 34!A1:I8';
       Value.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
  break;
}
}

Google Sheets Macro switch Statements

1 Answers