CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mark Arnold
Mark Arnold

Reputation: 21

Google Sheet Script Moving across Columns

I am trying to write a Google Sheets Script that:

  1. If LF1 = “Forecast”,
  2. Clears the contents of cell LF11,
  3. Then waits for the calculation to update
  4. Then copies the contents from LF8
  5. Then pastes the contents from LF8 into LF11
  6. The script then repeats over and over moving across columns one by one where row 1 = “Forecast” and stops when row 1 <> “Forecast”

I am a complete beginner and have only managed to get this far:

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

        var sheet = ss.getSheets()[0];

        var range = sheet.getRange("LF11:LI11");
        range.clearContent();

        var sheet = SpreadsheetApp.getActiveSheet();
        sheet.getRange("LF8:LI8").copyTo(sheet.getRange("LF11:LI11"), {contentsOnly:true});
    }

Any help would be greatly appreciated!

Upvotes: 2

Views: 73

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

The code would probably look something like this. I haven't tested it. Note the Logger.log('myVariableName: ' + myVariableName); statements. To see the print out from the Logger.log() statements, use the "View" menu, and choose the "Logs" menu item.

function UpdateDrawDownToAvailable() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheets()[0];

  var rangeForRow11 = sheet.getRange("LF11:LI11");
  var row11_Values = rangeForRow11.getValues(); //A 2 Dimensional array of values.
  row11_Values = row11_Values.join().split(","); //One dimension array of values
  Logger.log('row11_Values: ' + row11_Values);

  var rangeForRow8 = sheet.getRange("LF8:LI8");
  var row8_Values = rangeForRow8.getValues(); //A 2 Dimensional array of values.
  row8_Values = row8_Values.join().split(","); //One dimension array of values
  Logger.log('row8_Values: ' + row8_Values);

  var i=0,
      thisLoopCellValue = "",
      row8Value="";

  for (i=0;i<row11_Values.length;i+=1) { //Loop the number of times that the array is long
    thisLoopCellValue = row11_Values[i];
    Logger.log('thisLoopCellValue: ' + thisLoopCellValue);

    if (thisLoopCellValue === "Forecast") {
      sheet.getRange(11, 325+(i+1)).clear(); //Clear the cell
      SpreadsheetApp.flush(); //Apply all pending changes
      //Get row 8 value
      row8Value = row8_Values[i];
      Logger.log('row8Value: ' + row8Value);
      sheet.getRange(11, 325+(i+1)).setValue(row8Value);//Put row 8 value into row 11
    } else { //If this cell value does not equal to "Forecast"
      return; //Quit
    };

  };
};

Upvotes: 1

Related Questions