0

I have a spreadsheet that contains 3 columns with leave request data coming from a google form.

[From Date]   [To Date]   [Name of the Requestor]
  5/16/2018   5/18/2018   John
  5/18/2018   5/20/2018   Bob
  6/31/2018    6/4/2018   David
   6/4/2018    6/5/2018   Kenneth

Then I created this formula to list down the date inclusions and name.

={ArrayFormula(B2+row(indirect("B1:B"& C2-B2+1))-1),TRANSPOSE(SPLIT(REPT(D2&",",C2-B2+1),","))}

The output will look like this:

5/16/2018   John
5/17/2018   John
5/18/2018   John

Then I created a loop via app script to increment the formula everytime a request is added on the table but I can't seem to make it right.

The expected output should somehow look like this:

5/16/2018   John
5/17/2018   John
5/18/2018   John
5/18/2018   Bob
5/19/2018   Bob
5/20/2018   Bob
5/31/2018   David
 6/1/2018   David
 6/2/2018   David
 6/3/2018   David
 6/4/2018   David
 6/4/2018   Kenneth
 6/5/2018   Kenneth

Currently I have this:

var formula = '={ArrayFormula(B2+row(indirect("B1:B"& C2-B2+1))-1),TRANSPOSE(SPLIT(REPT(D2&",",C2-B2+1),","))}';

function test(){
var testSS =SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var testRange = testSS.getDataRange().getValues();
var lastrow = testSS.getLastRow();
var test = testSS.getRange(2, 5,lastrow).getValues();

for(var i=0;i<testRange.length;i++){
if(test[i] == ""){
             testSS.getRange(2,5).setFormula(formula);
             }
             }
}

Basically, the script should setformula on the first empty cell ('E2:E') in column E (in this scenario, the formula will place values on first three rows in column E range) then rechecks the column E again to find the first empty cell (which will be E5) and loop the incremented formula.

Here is the link to my spreadsheet: SAMPLE SPREADSHEET

  • What do you mean by "increment the formula everytime a request is added on the table"? How should look the formula after it was "incremented"? – Rubén Apr 25 '18 at 22:00
  • When a user submits a leave request through google form, it goes to the spreadsheet, that's what I meant when I said request is added on the table. – Zyre Soriano Apr 26 '18 at 13:06
  • The formula should look like this on column E: Cell E2'='{ArrayFormula(A2+row(indirect("a1:a"& B2-A2+1))-1),TRANSPOSE(SPLIT(REPT(C2&",",B2-A2+1),","))}' , Cell E5= '={ArrayFormula(A3+row(indirect("a1:a"& B3-A3+1))-1),TRANSPOSE(SPLIT(REPT(C3&",",B3-A3+1),","))}' , Cell E8 = '={ArrayFormula(A4+row(indirect("a1:a"& B4-A4+1))-1),TRANSPOSE(SPLIT(REPT(C4&",",B4-A4+1),","))}' , Cell E13= '={ArrayFormula(A5+row(indirect("a1:a"& B5-A5+1))-1),TRANSPOSE(SPLIT(REPT(C5&",",B5-A5+1),","))}' – Zyre Soriano Apr 26 '18 at 13:13
  • Please edit your question to add to it all the relevant details. Comments are mostly to make constructive criticism / suggest improvements / request clarifications. – Rubén Apr 26 '18 at 14:11
  • The code in the spreadsheet doesn't match the code in the question and also it looks that the title doesn't match what you was looking. If you still need help please edit your question. – Rubén May 27 '18 at 16:02
0

Basically, the script should setformula on the first empty cell ('E2:E') in column E (in this scenario, the formula will place values on first three rows in column E range)

Use sheet.getLastRow() to find the last row coordinate with data, then add 1 to get the first empty row coordinate and get the range to post the formula.

then rechecks the column E again to find the first empty cell (which will be E5) and loop the incremented formula

Before rechecking could be necessary to do a Spreadsheet.flush() to update the spreadsheet with the change made previously.

Another approach could be to get all the required formulas, make an array of formulas and use setFormulas() to add all of then at once.

  • I edited my code but it's still not working. Can you show me how the code should look like? I pasted a link of my spreadsheet so you can take a look at my script as well – Zyre Soriano Apr 26 '18 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.