CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
greeny
greeny

Reputation: 445

Consolidate Code and Neaten Up in Google Sheets Script

I have a dataset in Google Sheets with a script below. I want to be able to neaten this up and consolidate the number of lines of code, specifically all the sheet.getRange statements

      sheet.appendRow([
      "",
      "",
      "",
      "",
      sheet.getRange(selectedRow,5).getValue(),
      number+1,
      sheet.getRange(selectedRow,7).getValue(),
      sheet.getRange(selectedRow,8).getValue(),
      sheet.getRange(selectedRow,9).getValue(),
      sheet.getRange(selectedRow,10).getValue(),
      sheet.getRange(selectedRow,11).getValue(),
      newDate,
      sheet.getRange(selectedRow,13).getValue(),
      sheet.getRange(selectedRow,14).getValue(),
      sheet.getRange(selectedRow,15).getValue(),
      sheet.getRange(selectedRow,16).getValue(),
      sheet.getRange(selectedRow,17).getValue(),
      sheet.getRange(selectedRow,18).getValue(),
      sheet.getRange(selectedRow,19).getValue(),
      sheet.getRange(selectedRow,20).getValue(),
      sheet.getRange(selectedRow,21).getValue(),
      sheet.getRange(selectedRow,22).getValue(),
      sheet.getRange(selectedRow,23).getValue(),
      sheet.getRange(selectedRow,24).getValue(),
      sheet.getRange(selectedRow,25).getValue(),
      sheet.getRange(selectedRow,26).getValue(),
      sheet.getRange(selectedRow,27).getValue(),
      sheet.getRange(selectedRow,28).getValue(),
      sheet.getRange(selectedRow,29).getValue(),
      sheet.getRange(selectedRow,30).getValue(),
      sheet.getRange(selectedRow,31).getValue(),
      sheet.getRange(selectedRow,32).getValue(),
      sheet.getRange(selectedRow,33).getValue(),
      sheet.getRange(selectedRow,34).getValue(),
      sheet.getRange(selectedRow,35).getValue()
      ]);
  }

If you could show me the code on how to reduce this it would be appreciated!

Upvotes: 0

Views: 35

Answers (1)

Kin Siang
Kin Siang

Reputation: 2699

I can see there is a pattern on your execution, therefore you don't have to write all the getvalue() since most of them is directly obtain from the range value without editing.

GetValues() is faster method to update all the value at once and you can change some of the value in the array by referring to its relevant position. At last, appendrow required 1D array therefore you need to convert it using array.flat():

Code enhancement:

 function app1() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      var range1 = sheet.getRange(selectedRow,1,1,35).getValues();
      var number = 20;
    
      range1[0][0] = '';
      range1[0][1] = '';
      range1[0][2] = '';
      range1[0][3] = '';
      range1[0][5] = number + 1;
      range1[0][11] = new Date();
    
      sheet.appendRow(range1.flat());
    }

Upvotes: 1

Related Questions