CODEWITHSUNDEEP
javascriptloopsgoogle-apps-scriptgoogle-sheets
Paul Costa
Paul Costa

Reputation: 23

Get Values from one google sheet enter it onto another sheet with a 4 loop

I have to spreadsheets. I want the program to look at Row A on spreadsheet Ind and see if it is a 1 or 0. if it is a one on the active sheet "return" I want it to grab the date from Row D in spreadsheet "Ind" and post it onto Spreadhseet "return". I can't figure this out and I have it working on VBA in excel.

Any help would be greatly appreciated. 

function myFunction() {

 X = 5;
 Y = 2;
 Z = 1;
 Count = 4560;
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var source_sheet = ss.getSheetByName("Ind");
 var target_sheet = ss.getSheetByName("Returns");

 while (Z < Count){

   if (source_sheet.getRange("A" & X) = 1) { 
      var buydate = source_sheeet.getRange("D" & X).getValues()
      target_sheet.getRange("A" & Y) = buydate
      target_sheet.getRange("B" & Y) = "Buy"
      Y = Y + 1
   } else if (source_sheeet.Range("C" & X) = 2) {
      var selldate = source_sheeet.Range("D" & X).getvalues()
      target_sheet.getRange("A" & Y) = selldate
       target_sheet.getRange("B" & Y) = "Sell"
       Y = Y + 1
   }

    X = X + 1
   Z = Z + 1
  }}

Upvotes: 0

Views: 137

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

This line:

if (source_sheet.getRange("A" & X) = 1) {

Is using an ampersand, and it should be a plus sign. To concatenate strings in JavaScript, use a plus sign.

Also, source_sheet.getRange() will return a range, not a value, so it's never going to equal 1. You would need to use something like the following:

if (source_sheet.getRange("A" + X.toString()).getValue() === 1) {

And use triple equal signs for an equality check. JavaScript is constantly attempting to coerce variables into the type that seems correct. So, it might convert the number in the variable "X" to a string, but you can also use the toString() method.

getValues() returns a two-dimensional array. Each inner array represent a row. Each element in the inner array represents a cell in a row.

If you only want to get one value, use getValue() (no "s" on the end) instead of getValues().

var buydate = source_sheet.getRange("D" + X.toString()).getValue();

You are trying to set the value by using an equal sign. That won't work. You need to use the setValue() or setValues() method.

target_sheet.getRange("A" + Y.toString()).setValue(buydate);

By not using the var key word in your assignments, the variables automatically become "global" variables.

X = 5;
Y = 2;
Z = 1;

There's no need to make them global variables in this case, I don't think.

var X = 5,
    Y = 2,
    Z = 1;

You can declare multiple variables all at the same time.

Upvotes: 1

Related Questions