CODEWITHSUNDEEP
loopsgoogle-apps-scriptgoogle-sheetsclosures
García
García

Reputation: 73

Google Scripts For Loop

I'm trying to insert some data from a spreadsheet into a different spreadsheet, the problem is that the loop is not behaving as expected, it only gives me one entry in the target spreadsheet. I've tried using while and no function but it didn't work.

Here is the code:

function move(){
  var homeBook = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = homeBook.getSheets()[0];
  var limit = sheet.getLastRow(); //number of rows in the sheet

  var evento = sheet.getRange(2, 1, limit-1).getValues(); //event list
  var descript = sheet.getRange(2,2,limit-1).getValues(); //description list
  var tags = sheet.getRange(2,3,limit-1).getValues(); //tag list
  var sheetsIDHome = sheet.getRange(2,4,limit-1).getValues(); //ID list


  var targetBook = SpreadsheetApp.openById("1t3qMTu2opYffLmFfTuIbV6BrwsDe9iLHZJ_ZT89kHr8"); //target workbook
  var target = targetBook.getSheets()[0]; //Sheet1
  var targetLimit =target.getLastRow(); //Rows with content
  var sheetsIDTarget = target.getRange(targetLimit, 4); // ID list
  var targetRow = targetLimit+1; //row where content is going to be inserted

for(i = 2;i <= limit;i++){//loop for each value to be inserted in each row of the target sheet
(function(x){
          target.getRange(targetRow,1).setValue(x);
          target.getRange(targetRow,2).setValue(descript[2]);
          target.getRange(targetRow,3).setValue(tags[3]);
          target.getRange(targetRow,4).setValue(sheetsIDHome[4]);
          targetRow = targetRow++; 
      })(i);
  };

Upvotes: 7

Views: 115313

Answers (2)

jim
jim

Reputation: 1

there is a code that has been mis-placed, kindly check my code below:

   function UpdateBSRSpecial()
    {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var testForm = ss.getSheetByName("ENTRY FORM");
        var testTable = ss.getSheetByName("BSR DATA");
        var testFormValue = testForm.getRange("G6").getValue();
        var rangeData = testTable.getDataRange();
        var lastColumn = rangeData.getLastColumn();
        var lastRow = rangeData.getLastRow();
       for(var i=2;i<=lastRow;i++){
           var dataID = testTable.getRange(i,1).getValue();
          if(testFormValue == dataID)
        {
            testTable.getRange(i,6).setValue("new value");
        };
      }; 
    };

hope this helps

Upvotes: 0

WhiteHat
WhiteHat

Reputation: 61230

You're trying to access the four arrays created from the values for columns 1-4.

Your for statement needs to match their structure, starting with the first array instance of 0. You can use any of the arrays for the iteration, I've chosen the first.

In addition, I've removed the function and replaced x with the instance from evento.

++ increments the value of the variable, no need for assignment there.

for (var i = 0; i < evento.length; i++) {
  target.getRange(targetRow,1).setValue(evento[i]);
  target.getRange(targetRow,2).setValue(descript[i]);
  target.getRange(targetRow,3).setValue(tags[i]);
  target.getRange(targetRow,4).setValue(sheetsIDHome[i]);
  targetRow++; 
}

Upvotes: 8

Related Questions