CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
joanna
joanna

Reputation: 3

Script to copy from one sheet to another, needs edit

I have this script which is working well, but i need to edit it to a) only return new rows since last run b) only return certain cells instead of whole row

any guidance would be greatly appreciated

function Copy() {
var sourceSheet = SpreadsheetApp.openById('1WAtRDYhfVXcBKQoUxfTJORXwAqYvVG2Khl4GuJEYSIs')
    .getSheetByName('Jobs Log');
var range = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), sourceSheet.getLastColumn());
var arr = [];
var rangeval = range.getValues()
    .forEach(function (r, i, v) {
        if (r[1] == 'Amber') arr.push(v[i]);
    });
var destinationSheet = SpreadsheetApp.openById('137xdyV8LEh6GAhAwSx4GmRGusnjsHQ0VGlWbsDLXf2c')
    .getSheetByName('Sheet1');
destinationSheet.getRange(destinationSheet.getLastRow() + 1, 1, arr.length, arr[0].length)
    .setValues(arr);

}

Upvotes: 0

Views: 90

Answers (1)

Vytautas
Vytautas

Reputation: 2286

In order to only check new data added after last runtime we have to store .getLastRow() value in properties and retrieve it every runtime. We would also have to work under a few assumptions:

  1. In the input data new values are only appended at the bottom and never inserted between other data
  2. Data is never deleted from the input sheet (if you ignore this, then you must also have an update script for the last row that runs after deleting data)
  3. The sheet is not sorted after new data is added but before this script is run.

So you would need something along the lines of

var sourceSheet = SpreadsheetApp.openById('1WAtRDYhfVXcBKQoUxfTJORXwAqYvVG2Khl4GuJEYSIs')
    .getSheetByName('Jobs Log');
var lastRow = sourceSheet.getLastRow();

// note that you need to hav the script property initialized and stored
// or adjust the if to also check if prevLastRow gets a value
var prevLastRow = PropertiesService.getScriptProperties().getProperty('lastRow')

if (lastRow <= prevLastRow) {
  return; // we simply stop the execution right here if we don't have more data
}
// then we simply start the range from the previous last row
// and take the amount of rows added afterwards
var range = sourceSheet.getRange(prevLastRow,
                                 1,
                                 lastRow - prevLastRow,
                                 sourceSheet.getLastColumn()
                                );

As for the second question, inside the forEach you need to simply push an array into arr that will contain only the columns you want. So for example

if (r[1] == 'Amber') arr.push(v[i]);

changes into

if (r[1] == 'Amber') arr.push([v[i][0], v[i][3], v[i][2]]);

which will output A D C columns (in that order) for each row.

Finally, the last thing you need to run before the script ends is

PropertiesService.getScriptProperties().setProperty('lastRow', lastRow)

which will let us know where we stopped the next time we run the script. Again, keep in mind that this works only if new data will always be in new rows. Otherwise, you need to do a different method and retrieve 2 arrays of data. 1 for the entire input sheet and 1 for the output sheet. Then you would have to perform 2 if checks. First one to see if your criteria are met and a second one to see if it already exists in the output data.

Upvotes: 2

Related Questions