CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-appsgoogle-forms
Thunderbog
Thunderbog

Reputation: 11

setFormulas to a new spreadsheets when created

I have been searching through question and example everywhere but I can not seem to find an answer. I need to set these formulas in the header row and columns AQ:1 to AV:1(AQ1:AV1).

["={"Hours:";ArrayFormula((IF(LEN(AE2:AE),((AF2:AF-AE2:AE)*24)+(((T2:T-S2:S)*24))+(((I2:I-H2:H)*24)),)))}",
"={"Rest Hours:";ArrayFormula((IF(LEN(AE2:AE),((M2:M)*24)+(((X2:X)*24))+(((AJ2:AJ)*24)),)))}",
"={"Rest Hours Wages:";ArrayFormula((IF(LEN(AE2:AE),(AI2:AI*(AJ2:AJ*24)+(L2:L*(M2:M*24)+(W2:W*(X2:X*24)))),)))}",
"={"Hours-RestHours:";ArrayFormula((IF(LEN(AE2:AE),((AQ2:AQ-AR2:AR)),)))}",
"={"Rate:";ArrayFormula((IF(LEN(AE2:AE),((E2:E+P2:P+AB2:AB)),)))}"],

and I need to put this into the second row of column AQ (AQ2). 

["=ArrayFormula((IF(LEN(AE2:AE),((AT2:AT*AU2:AU)+AS2:AS),)))}"],

I keep getting this error message and I cannot save the script.

Missing ] after element list. (line 138, file "Code")

I think I know why but I need the headers to be titled with the correct text because I use the headers in other functions.

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

// This sets the formulas to be a row of sums, followed by a row of averages right below.
// The size of the two-dimensional array must match the size of the range.
var formulas = [
  ["={"Hours:";ArrayFormula((IF(LEN(AE2:AE),((AF2:AF-AE2:AE)*24)+(((T2:T-S2:S)*24))+(((I2:I-H2:H)*24)),)))}",
  "={"Rest Hours:";ArrayFormula((IF(LEN(AE2:AE),((M2:M)*24)+(((X2:X)*24))+(((AJ2:AJ)*24)),)))}",
  "={"Rest Hours Wages:";ArrayFormula((IF(LEN(AE2:AE),(AI2:AI*(AJ2:AJ*24)+(L2:L*(M2:M*24)+(W2:W*(X2:X*24)))),)))}",
  "={"Hours-RestHours:";ArrayFormula((IF(LEN(AE2:AE),((AQ2:AQ-AR2:AR)),)))}",
  "={"Rate:";ArrayFormula((IF(LEN(AE2:AE),((E2:E+P2:P+AB2:AB)),)))}",
  "={"Wages:";ArrayFormula((IF(LEN(AE2:AE),((AT2:AT*AU2:AU)+AS2:AS),)))}"],
  ["=ArrayFormula((IF(LEN(AE2:AE),((AT2:AT*AU2:AU)+AS2:AS),)))}"],
 ];

var cell = sheet.getRange("AQ1:AW2");
 cell.setFormulas(formulas);
}

Am I missing something or am I doing it completely wrong and need to find another way to do it. I also need to add the formulas to every sheet in the spreadsheet but I have not been able to test if it works. Thank you in advance for any help.

Upvotes: 0

Views: 104

Answers (1)

Viktor Zhurbin
Viktor Zhurbin

Reputation: 133

UPD:

It seems like double quotes are interfering here. To avoid this, you can use single quotes for formula enclosure and double quotes within formula, like this:

'={"Hours:";ArrayFormula((IF(LEN(AE2:AE),((AF2:AF-AE2:AE)*24)+(((T2:T-S2:S)*24))+(((I2:I-H2:H)*24)),)))}'

As mentioned in comments:

  1. Arrays would expand so you are likely to need setting formulas in one row, like AQ1:AW1
  2. Besides, if there's source data in columns AQ:AU (by the looks of formulas), setting array formulas in the same columns will most likely return error: Array result was not expanded because it would overwrite data in AQ18.
  3. You seem to have an extra } at the end of the last formula

Try this and adjust to your needs:

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

// This sets the formulas to be a row of sums, followed by a row of averages right below.
// The size of the two-dimensional array must match the size of the range.
var formulas = [
  ['={"Hours:";ArrayFormula((IF(LEN(AE2:AE),((AF2:AF-AE2:AE)*24)+(((T2:T-S2:S)*24))+(((I2:I-H2:H)*24)),)))}',
  '={"Rest Hours:";ArrayFormula((IF(LEN(AE2:AE),((M2:M)*24)+(((X2:X)*24))+(((AJ2:AJ)*24)),)))}',
  '={"Rest Hours Wages:";ArrayFormula((IF(LEN(AE2:AE),(AI2:AI*(AJ2:AJ*24)+(L2:L*(M2:M*24)+(W2:W*(X2:X*24)))),)))}',
  '={"Hours-RestHours:";ArrayFormula((IF(LEN(AE2:AE),((AQ2:AQ-AR2:AR)),)))}',
  '={"Rate:";ArrayFormula((IF(LEN(AE2:AE),((E2:E+P2:P+AB2:AB)),)))}',
  '={"Wages:";ArrayFormula((IF(LEN(AE2:AE),((AT2:AT*AU2:AU)+AS2:AS),)))}',
  '=ArrayFormula((IF(LEN(AE2:AE),((AT2:AT*AU2:AU)+AS2:AS),)))']
 ];

var cell = sheet.getRange("AW1:BC1");
 cell.setFormulas(formulas);
}

Upvotes: 2

Related Questions