Google sheets - Combining Google sheet scripts

[Eric Morandeau]

Hi!

I have this sheet that runs multiple onEdit's and time triggered scripts.

Most of the scripts have the same function but has different triggers and it slows down my sheet overall. I'm not an expert at this so and I'm looking to combine scripts so I can keep the amount scripts to a minimum.

Any help would be appreciated.

There is 2 types of functions that I'm looking for to combine.

The first one

This function moves rows from a main sheet called Pipe to different destination sheets within the spreadsheet when triggered

The second one

This function makes counts to a backlog sheet when cell/columns are edited in the same main sheet called Pipe

Here is a link to a copy of my sheet with the scripts in it: https://docs.google.com/spreadsheets/d/17RTTZmoIRpA4uFkaLFuFZJSPDW5sRhEQgB8B4mwln5U/edit?usp=sharing

Here are all of the scripts that I'm looking to combine down below.

First function script

​function BokadM1() {

var ss = SpreadsheetApp.getActiveSpreadsheet(); 

var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var r = SpreadsheetApp.getActiveSpreadsheet().getActiveRange(); if(s.getName() == "Pipe" && r.getColumn() == 8 && r.getValue() != "") { var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Bokad");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent(); } } 

function Ombokad() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var r = SpreadsheetApp.getActiveSpreadsheet().getActiveRange(); if(s.getName() == "Pipe" && r.getColumn() == 13 && r.getValue() != "") { var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Bokad");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent(); } }

function ÅkM1() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var r = SpreadsheetApp.getActiveSpreadsheet().getActiveRange(); if(s.getName() == "Pipe" && r.getColumn() == 7 && r.getValue() != "") { var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Återkomster");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent(); } }

function NejM1() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var r = SpreadsheetApp.getActiveSpreadsheet().getActiveRange(); if(s.getName() == "Pipe" && r.getColumn() == 9 && r.getValue() != "") { var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Nej");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent(); } }

function AvtalM1() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var r = SpreadsheetApp.getActiveSpreadsheet().getActiveRange(); if(s.getName() == "Pipe" && r.getColumn() == 10 && r.getValue() != "") { var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Avtal");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent(); } }

function Snitt() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var r = SpreadsheetApp.getActiveSpreadsheet().getActiveRange(); if(s.getName() == "Pipe" && r.getColumn() == 8 && r.getValue() != "") { var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Snitt"); var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); s.getRange(row, 5, 1, 1).copyTo(target); } }

Second function script

function Samtal(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 1; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 5) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); } 

function Samtal2(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 15; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 5) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Email(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 2; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 6) { return; } // increment count const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Email2(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 16; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 6) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Åk(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 3; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 7) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Åk2(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 17; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 7) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Bokad(e) {

if (e.oldValue) return;

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 4; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 8) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Bokad2(e) {

if (e.oldValue) return;

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab const countColumn = 18; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.oldvalue == "" || e.range.columnStart !== 8) { return; } // increment count const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Nej(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 5; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 9) { return; } // increment

count const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Nej2(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 19; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 9) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Avtal(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 6; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 10) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Avtal2(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 20; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 10) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Gmöte(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 7; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 12) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Gmöte2(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 21; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 12) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Offert(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 8; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 14) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }

function Offert2(e) {

const sheetToWatch = 'Pipe';

const targetSs = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1rRENZjpQzdq0xugBPkMjltz2AIpYPWHH_sThh5r9x3A/edit#gid=618446899") //id of spreadsheet

const targetSheet = 'Brain'; //fill in name of other sheet/tab

const countColumn = 22; // column G

const sheet = e.range.getSheet(); if (!e.value || sheet.getName() !== sheetToWatch || e.range.rowStart <= (sheet.getFrozenRows() || 1) || e.range.columnStart !== 14) { return; } // increment count

const countCell = targetSs.getSheetByName(targetSheet).getRange(e.range.rowStart, countColumn); countCell.setValue((Number(countCell.getValue()) || 0) + 1); }​

[Edward Wu]

For starters, consider combining the separate functions into fewer functions, and use either nested If statements or a Switch to narrow down the choices.

Also, using the edit token "e" to get the value *once*, instead of multiple r.getValue() should speed things up.

Doing a getValue() is relatively slow, so if you have to do it multiple times, that'll really slow things down.

Something like this:

function allInOne(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var r = e.range;

var sheet = range.sheet;

var sheetName = sheet.getName();

if (e.value != "") {

if (sheetName == "Pipe" {

switch (r.getColumn()) {

case 7:

// do column 7 tasks

break;

case 8:

// do column 8 tasks

break;

case 13:

// do column 13 tasks

break;

default:

// do nothing because the edit is happening on a column we don't want to watch

return;

}

}

}

}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d63bacea-1a24-4567-9881-a68f258ea33cn%40googlegroups.com.

[cbmserv...@gmail.com]

Edward’s suggestion is good to narrow down what you want to get done rather than try all everytime.

 

But I scanned all the functions you have listed and most would proceed fairly quickly with the exception of the Move functions.

 

The Move functions are quite inefficient and depending on how many rows of data you have in those sheets, it could really slow down the spreadsheet. Also, I don’t know why you have an individual function for each sheet, you should probably only do one function for all and have it run through a for loop to go through all the sheets instead.

 

I put a new function called MoveAll in your Copy sheet. It will do the function of all three move functions you had as well as do them much much faster. Caution: I have not tested it, but it should work 😊

 

Remove all your move functions from onEdit and just add the MoveAll and try it out.

 

Let me know.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABNTzmBjUbgUMQJ4v12ZPfB7B44UEBstQJSMU2xxt2MF1hd7hw%40mail.gmail.com.

[Eric Morandeau]

Hi!

I tried the Move function and it's not working, my guess is that the dates where the scripts should trigger is in different col's on the different sheets. 

The reason for this is that I put in the date in different Col's in the main sheet depending on what it is for ex. if it's just a call back its one col or if its a booked meeting its another col. After that the row gets moved to a different sheets, one for callbacks, one for bookings, one for agreements. So the script needs to watch the different Col's on those sheets to move them.

Is it possible to edit this to a range since it only will look for dates dateCell = sheetData[i][8]; , to something like this dateCell = sheetData[i][7:10];?

[Eric Morandeau]

Hi Edward!

Thanks for replying to me.

That is a great idea and just so I understand this since I'm not a expert on this:

Do you mean something like this?

function allInOne(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var r = e.range;

var sheet = range.sheet;

var sheetName = sheet.getName();

if (e.value != "") {

if (sheetName == "Pipe" {

switch (r.getColumn()) {

case 7:

if(r.getColumn() == 7 && r.getValue() != "") {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Återkomster");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent();

}

}// do column 7 tasks

break;

case 8:

if(r.getColumn() == 8 && r.getValue() != "") {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Bokad");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent();

}

}// do column 8 tasks

break;

case 13:

if(r.getColumn() == 13 && r.getValue() != "") {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Bokad");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent();

}

[Eric Morandeau]

I'm trying to implement the switch statement, I removed the if statement from all cases since it don't serve any value when using switch?

Besides from that I'm getting this Syntax Error referring to the switch statement and I don't know why that is. 

Can switch statement be inside a if statement or should it be declared before the if statement?

[cbmserv...@gmail.com]

I see, yes, having different Column number for where the dates are stored is an issue for a common function. I put in some code to get around that by using an array where the Column numbers are stored for each sheet.

 

Try it now and see if that helped.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/afe03b39-2e75-4288-8894-13f747c2ae1dn%40googlegroups.com.

[Edward Wu]

Hi Eric, yes you've got it. Be careful about the brackets though. You don't need it between "cases". And the if statements "inside" the cases are redundant.

Here's your sample code slightly cleaned up with the unnecessary if statements commented out:

function allInOne(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var r = e.range;

var sheet = range.sheet;

var sheetName = sheet.getName();

if (e.value != "") {

if (sheetName == "Pipe" {

switch (r.getColumn()) {

case 7:

// if (r.getColumn() == 7 && r.getValue() != "") { // you don't need this because the "if (e.value != "")" above and the "case" takes care of it

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Återkomster");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent();

// }

}// do column 7 tasks

break;

case 8:

// if (r.getColumn() == 8 && r.getValue() != "") {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Bokad");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent();

// }

// do column 8 tasks

break;

case 13:

// if (r.getColumn() == 13 && r.getValue() != "") {

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/23e67d81-1518-40d0-ba75-41bc80ad762fn%40googlegroups.com.

[Eric Morandeau]

Hi! 

I tried the script and it does almost the job, now it just deletes the row and it doesn't move the row back to the Pipe sheet.

I can see on the last line there is no move function if I'm not mistaken.

Any ideas?

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/ub1R4fcVaRI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/006401d977c1%242e204300%248a60c900%24%40gmail.com.

--

 

Eric Morandeau
Account Executive

Mob nr: 070-781 92 34

E-mail: eric.mo...@mynewsdesk.com

Rosenlundsgatan 40
118 53 Stockholm
Sverige

DIGITAL COMMUNICATIONS & PR MADE EASY
Simplify your workflow, expand your influence and get results with one complete communications solution.
Monitor what’s being said. Create and publish stories. Engage with your audience. Analyze results.

www.mynewsdesk.com

Your personal data will be processed in accordance with GDPR (EU 2016/679). For more information, please read our Privacy Policy.

[cbmserv...@gmail.com]

The script does append the row of data to Pipe and then deletes the row.

 

Check at bottom of Pipe spreadsheet. (you may have a bunch of blank rows in between the top portion and bottom portion of spreadsheet.

 

Here is the code that does the append:

 

        if(test <= today){

          

          targetSh.appendRow(sheetData[i]);

          sourceSh.deleteRow(i+1);

        }

 

You can see it adds the new row to Pipe and then deletes it from one of those sheets. Both actions one after the other.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADijnXDoovK%2BCT0yXvu-NYbFaVpTy-NFFuRYiSoLSnfUT7J-7A%40mail.gmail.com.

[Eric Morandeau]

Hi! 

Yes that is correct, I didn't look all the way down. Thanks a lot for the help so far.

The last thing I need to figure out is to make them end up on Col B, now they end up on Col A and that is not what I want since I have data in that Col.

I used this function in the old script to solve this problem. 

function getLastRow_(sheet, columnNumber) {

// version 1.5, written by --Hyde, 4 April 2021

const values = (

columnNumber

? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)

: sheet.getDataRange()

).getDisplayValues();

let row = values.length - 1;

while (row && !values[row].join('')) row--;

return row + 1;

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/002e01d97931%246049ada0%2420dd08e0%24%40gmail.com.

[Eric Morandeau]

Hi Edward!

I tried the script and it gets 100% error. I don't know what is wrong with it, any ideas?

function allInOne(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var r = e.range;

var sheet = range.sheet;

var sheetName = sheet.getName();

if (e.value != "") {

if (sheetName == "Pipe")

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/ub1R4fcVaRI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABNTzmCnCYmS28Hiv4Ehan7L2Qprp5Z6ExRPPis9fnE1Tf_6aA%40mail.gmail.com.

[Ed Sambuco]

You have a typo ... there is no sheet object "s."  only "ss."  I see the erro scattered over your code.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADijnXCzKXebHD3LMKk7urFiXSsz_MWxkFBgBCicUgwRR%3D_GLg%40mail.gmail.com.