CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Bbrads
Bbrads

Reputation: 69

Running an IF script across multiple ranges

I've put together a script that takes the values of 4 cells in a row ,A23:E23, and duplicates those values in another sheet if a specific (H3) cell in that row contains the text "Live".

The script needs to run daily, which I understand I can do with ease, but it also needs to work with every row below row 23 that contains values, with the reportData and ifd variables working respectively. i.e.

var reportData = reportSheet.getRange("A24...A25...A26:E24...E25...E26").getValues();
var lastRow = recordsSheet.getLastRow();
var ifd = reportSheet.getRange("H24...H25...H25").getValue();

The idea behind this script is to capture a daily snapshot of the values that are in Sheet1 as the values are constantly updated, but as cumulative figures. I need to capture a daily record of these cumulative figures. 

function appendToRecords() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var reportSheet = ss.getSheetByName("Sheet1");
var recordsSheet = ss.getSheetByName("database")
var reportData = reportSheet.getRange("A23:E23").getValues();

var lastRow = recordsSheet.getLastRow();
var ifd = reportSheet.getRange("H23").getValue();  

  if (ifd == "Live"){
   recordsSheet.getRange("A2:E2")
          .setValues(reportData);  }}

Can anyone suggest a way to do this?

Here's a sample sheet that the script works with: https://docs.google.com/spreadsheets/d/1FMlj_9l7Sm0QMKcFpbC4iExbATgPL0jIWoVrvV_Vcfw/edit?usp=sharing

Thanks!

Upvotes: 0

Views: 60

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

You can easily log all "live" rows to your database like this:

function appendToRecords() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var reportSheet = ss.getSheetByName("Sheet1");
  var recordsSheet = ss.getSheetByName("database")
  var reportData = reportSheet.getRange("A8:H").getValues();

  // Get only rows that are "Live"
  // And only get the first five colums
  var toWrite = reportData.filter(function(row) {
    return row[7] === "Live"; 
  }).map(function(row) {
    return row.splice(0, 5);
  });

  if(!toWrite.length) {return;} // Return if nothing to write

  recordsSheet
      .getRange(recordsSheet.getLastRow() + 1, 1, 
                toWrite.length, toWrite[0].length)
      .setValues(toWrite);
}

Upvotes: 1

Related Questions