CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Oli Girling
Oli Girling

Reputation: 773

Google Script - Get Range from single cell

I am having some difficulties with something that seems really simple but I just cannot get my head around.

A single cell contains the text 'I8:I160'. This is the location of a range I want to refer to within the function. But no matter what I try I cannot seem to reference that location.

  1. I want SKUrange to refer to the cell Cal!G4 which contains the text I8:I160
  2. then SKUsheet to refer to the cell Cal!G8 which contains the text Pricelist!
  3. Then finally SKU to have the range of SKUrange.SKUsheet together (Pricelist!I8:I160)

Heres my code so far

function PasteValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

// 1.
  var SKUrange = ss.getRange('Cal!G4').getValue();
// 2.
  var SKUsheet = ss.getRange('Cal!G8').getValue();
// 3.
  var SKU = ss.getRange('Pricelist!I8:I160'); // this what I want to achieve

  var Buy = ss.getRange('Pricelist!A8:A160');
  var Convert = ss.getRange('Cal!G3').getValue();
  var Convertdollars = ss.getRange('Cal!M6').getValue();
  var Converteuros = ss.getRange('Cal!M7').getValue();
  var Sell = ss.getRange('Output!C2:C160');

  SKU.copyTo(ss.getRange('Output!A2'), {contentsOnly: true});
  Buy.copyTo(ss.getRange('Output!B2'), {contentsOnly: true});
  Buy.copyTo(ss.getRange('Output!C2'), {contentsOnly: true});

}

And here is a screenshot incase that helps enter image description here

UPDATD: THIS IS NOW WORKING - hope this helps someone

function PasteValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var SKUrange = ss.getRange('Cal!C4').getValue();
  var SKUsheet = ss.getRange('Cal!C8').getValue();
  var SKU = ss.getRange(SKUsheet + SKUrange); 
  var Buy = ss.getRange('Pricelist!I8:I160');
  var Convert = ss.getRange('Cal!C3').getValue();
  var Convertdollars = ss.getRange('Cal!F4').getValue();
  var Converteuros = ss.getRange('Cal!F5').getValue();
  var Sell = ss.getRange('Output!C2:C160');

  SKU.copyTo(ss.getRange('Output!A2'), {contentsOnly: true});
  Buy.copyTo(ss.getRange('Output!B2'), {contentsOnly: true});
  Buy.copyTo(ss.getRange('Output!C2'), {contentsOnly: true});

Upvotes: 0

Views: 3117

Answers (1)

user3717023
user3717023

Reputation:

If cell Cal!G4 has the string "I8:I160" and cell Cal!G8 has the string "Pricelist", then after running the code

var SKUrange = ss.getRange('Cal!G4').getValue();
var SKUsheet = ss.getRange('Cal!G8').getValue();
var SKU = ss.getRange(SKUsheet + SKUrange);

the variable SKU will point to the range Pricelist!I8:I160. You can then use SKU.getValues() to access its values, and so on.

Upvotes: 2

Related Questions