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...
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);
};
2 Answers
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++;
}
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
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
