There is a googlesheet with 4 similar tabs (on each, the same table template, but different data) and one tab with a list of the names of the 4 first tabs. Example: https://docs.google.com/spreadsheets/d/1Xdws6Vr6YVXuj19mvpOvkpJokOfY2T7Sh1aMpous_Xw/edit#gid=0

What the script does: goes into each tab and adds a certain number of lines, copying the date from the previous line, increasing it by one month. Where to insert a line is determined by the place where the word "Picture" is written

What caused the problem: inserting rows occurs only for one cell on the sheet where Picture occurs. If Picture occurs 3 times for example, then lines will be inserted in only one place.

How to make the line inserted in all places on the sheet where there is a Picture?

Code (thanks to Ihor Husar for help and understanding):

function loop(howMany){ if(!howMany){howMany = 2;}; var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets();//returns an array of all sheets //this is just a different way of looping through the sheets for (var sheetId = 0; sheetId<sheets.length; sheetId++){ if(sheets[sheetId].getSheetName() != "All_base"){ var data = sheets[sheetId].getDataRange().getValues(); var insertAfter = 6; for(var i = 5; i<data.length; i++){ if(data[i][0] == "Picture"){ insertAfter = i-1; //it is actually -2, but since i starts from 0, it is already smaller by 1; //actual row of Picture cell is i+1, and 2 cells up is i+1-2 = i-1 }; }; sheets[sheetId].insertRowsAfter(insertAfter, howMany); //At this point we inserted rows var lastDate = sheets[sheetId].getRange(insertAfter,1).getCell(1,1).getValue();//the last specified date as a Date object for(var i=0; i<howMany; i++){ var newDate = new Date(lastDate.getTime()); newDate.setMonth(newDate.getMonth()+1+i);//automatically will increase year if the next month is in the next year. sheets[sheetId].getRange(insertAfter+1+i,1).getCell(1,1).setValue(newDate); }; }; }; }

    1 answer 1

    This cycle is written incorrectly:

     for(var i = 5; i<data.length; i++){ if(data[i][0] == "Picture"){ insertAfter = i-1; }; }; sheets[sheetId].insertRowsAfter(insertAfter, howMany);

    If a "Picture" is detected, the value of insertAfter changes ... and nothing else happens. But it was necessary to insert lines, just inside the loop and not after it.

    Here is a revised version:

     for (var i = 5; i < data.length; i++) { if (data[i][0] == "Picture") { insertAfter = i-1; sheets[sheetId].insertRowsAfter(insertAfter, howMany); } }

    However, there is another problem: the insertion process changes the numbers of subsequent lines, which leads to confusion. If you want to insert several lines in different places, you need to insert starting at the bottom, not at the top. That is, change the loop from for(var i = 5; i<data.length; i++) to for(var i = data.length-1; i>=5; i--) :

     for (var i = data.length-1; i >= 5; i--) { if (data[i][0] == "Picture") { insertAfter = i-1; sheets[sheetId].insertRowsAfter(insertAfter, howMany); } }
    • Thank you for your reply! But the problem still remained. Strings are added, but they are empty. cells with subsequent months are inserted only in one place, in another place (where the picture occurs) empty lines are inserted. Also, the search for the "picture" value is only in the first column; if you insert it into another place in the table, it does not find it .. - alunet
    • 2
      Search only in the first column, because it is written like this: if (data[i][0] == "Picture") . To search everywhere, you need to replace it with if (data[i].indexOf("Picture") != -1) - user176149
    • Cool, thanks again) It remains only to solve the moment with the insertion of cells with the date (now it is inserted only in one place, empty lines are added in the rest of the places). I tried to transfer the block from var lastDate to the same loop where the insertion of rows was transferred, but the error appears: "TypeError: Can not find the getTime function in the object" - alunet