Trying to clean up my GAS(Google App Script) code-CodePudding

I am trying to minimize the code im using, I have a decent understanding of Google App Script, but I feel like I am overlooking an easier way to structure my code, any help is appreciated!

const onOpen= () =>{
let ui = SpreadsheetApp.getUi()
    .createMenu('Data Management')
    .addItem('Import DWGSRRLH', 'retrieveSrrLH')
    .addItem('Import DWGSRRRH', 'retrieveSrrRH')
    .addItem('Import DWGRRCLL', 'retrieveRRCLL')
    .addItem('Import DWGFRTCLL', 'retrieveFrtCll')
    .addItem('Import DWGFRONTIDL', 'retrieveFrontIdlLamp')
    .addItem('Import DWGREARLH', 'retrieveRearLH')
    .addItem('Import DWGREARRH', 'retrieveRearRH')
    .addToUi()

SpreadsheetApp.getUi()
    .createMenu('Export Data')
    .addItem('Export to New Sheet', 'exportSelected')
    .addItem('Export as Pdf to Email', 'exportPdf')
    .addToUi()

DriveApp.getRootFolder()

DriveApp.Permission.EDIT 
}
 
const retrieveSrrLH = () =>{
let getSpreadsheet =         SpreadsheetApp.openById('1R1yxLKtauS5_VbVuaZFPSQ9Wbm9sG7iOiIAcb4pnUsM').getSheetByName('DWGSRRLH MAchine 1')

let spc1= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 12, 30, 1).getValues()
let spc2= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 15, 30, 1).getValues()
let spc3= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 18, 30, 1).getValues()
let spc4= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 21, 30, 1).getValues()
let spc5= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 24, 30, 1).getValues()
let spc6= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 27, 30, 1).getValues()
let spc7= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 30, 30, 1).getValues()
let spc8= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 33, 30, 1).getValues()
let spc9= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 36, 30, 1).getValues()
let spc10= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 39, 30, 1).getValues()
let spc11= getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29, 42, 30, 1).getValues()

let setSpreadsheet = SpreadsheetApp
    .openById('1iv9PKH7YILQFg9q9OAa-MODfoxTMvtXQbPMbxutMgnI').getSheetByName('DWGSRRLH Machine 1')

let spc1Range = setSpreadsheet.getRange(11,3,30, 1)
let spc2Range = setSpreadsheet.getRange(11, 4, 30, 1)
let spc3Range = setSpreadsheet.getRange(11, 5, 30, 1)
let spc4Range = setSpreadsheet.getRange(11, 6, 30, 1)
let spc5Range = setSpreadsheet.getRange(11, 7, 30, 1)
let spc6Range = setSpreadsheet.getRange(11, 8, 30, 1)
let spc7Range = setSpreadsheet.getRange(11, 9, 30, 1)
let spc8Range = setSpreadsheet.getRange(11, 10, 30, 1)
let spc9Range = setSpreadsheet.getRange(11, 11, 30, 1)
let spc10Range = setSpreadsheet.getRange(11, 12, 30, 1)
let spc11Range = setSpreadsheet.getRange(11, 13, 30, 1)


spc1Range.setValues(spc1)
spc2Range.setValues(spc2)
spc3Range.setValues(spc3)
spc4Range.setValues(spc4)
spc5Range.setValues(spc5)
spc6Range.setValues(spc6)
spc7Range.setValues(spc7)
spc8Range.setValues(spc8)
spc9Range.setValues(spc9)
spc10Range.setValues(spc10)
spc11Range.setValues(spc11)


}


const exportSelected = () =>{
let exActiveSheet = SpreadsheetApp.getActiveSpreadsheet()
let tool = exActiveSheet.getSheetName()
let clearRange = exActiveSheet.getRange(11, 3, 30, 16)

let getSeleceted = exActiveSheet.getRange(3, 2,46, 16).getValues()
getSeleceted.map((x)=>Number((x)).toFixed())
console.log(getSeleceted)

let newSheet = SpreadsheetApp.create(`${new Date()} Export Data for ${tool}`)
let ssId = newSheet.getId().toString()
console.log(ssId)

let openedSheet = SpreadsheetApp.openById(ssId)
let sheet = openedSheet.getActiveSheet().activate()
let newSheetRange = sheet.getRange('B3:Q48')

newSheetRange.setValues(getSeleceted)
clearRange.clearContent()
}
const exportPdf = () =>{
const email = Session.getActiveUser().getEmail()
let ss = SpreadsheetApp.getActiveSpreadsheet()
let sheet = ss.getActiveSheet()
let clearRange = ss.getRange(11, 3, 30, 16)
sheet.setHiddenGridlines(true)


const subject = `PDF generated from spreadsheet ${ss.getName()}`

const body = `Sent with Google Sheets`

let blob = DriveApp.getFileById(ss.getId()).getAs('application/pdf');

blob.setName(ss.getName()   '.pdf');




Define the scope
Logger.log(`Storage Space used: ${DriveApp.getStorageUsed()}`);

If allowed to send emails, send the email with the PDF attachment
if (MailApp.getRemainingDailyQuota() > 0)
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments: [blob],
    });

This code does work as intended, just trying to learn how to minimize my code and for my code to run more efficiently.

The function onOpen() creates 2 menus in my sheet, and I think it is written properly, however my code for "retrieveSrrLH" is very long, Each spc point is related to a point on a fixture, I record the data using an appSheet app, then I take that data and export it to a pdf.

Is there a way that I can minimize the code that stores the points?

Any help is greatly appreciated!

none of the data above will expose any company data, I have worded most of the descriptors in a different way

CodePudding user response：

Try something like this to make the retrieveSrrLH() function more manageable:

function retrieveSrrLH() {
  const getSpreadsheet = SpreadsheetApp
    .openById('1R1yxLKtauS5_VbVuaZFPSQ9Wbm9sG7iOiIAcb4pnUsM')
    .getSheetByName('DWGSRRLH MAchine 1');
  const numRows = 30;
  const firstColumn = 12;
  const lastColumn = 42;
  const numColumns = lastColumn - firstColumn   1;
  const skipColumns = 3;
  const values = getSpreadsheet
    .getRange(getSpreadsheet.getLastRow() - numRows   1, firstColumn, numRows, numColumns)
    .getValues()
    .map(row => row.filter((value, columnIndex) => columnIndex % skipColumns === 0));
  SpreadsheetApp
    .openById('1iv9PKH7YILQFg9q9OAa-MODfoxTMvtXQbPMbxutMgnI')
    .getRange('DWGSRRLH Machine 1!C11')
    .offset(0, 0, values.length, values[0].length)
    .setValues(values);
}

See Apps Script at Stack Overflow and Clean Code JavaScript.

CodePudding user response：

This is how I would do the first function. Use one getValues() and one setValues() to minimize the calls to the server. It could probably be simplified since you are using every third column but I left it this way in case there isn't a pattern.

const retrieveSrrLH = () =>{
  let getSpreadsheet = SpreadsheetApp.openById('1R1yxLKtauS5_VbVuaZFPSQ9Wbm9sG7iOiIAcb4pnUsM').getSheetByName('DWGSRRLH MAchine 1')

  let values = getSpreadsheet.getRange(getSpreadsheet.getLastRow()-29,12,30,31).getValues();

  values = values.map( row => [row[0],row[3],row[6],row[9],row[12],row[15],row[18],row[21],row[24],row[27],row[30]]);

  let setSpreadsheet = SpreadsheetApp.openById('1iv9PKH7YILQFg9q9OAa-MODfoxTMvtXQbPMbxutMgnI').getSheetByName('DWGSRRLH Machine 1')

  setSpreadsheet.getRange(11,3,30,11).setValues(values);

}

Reference

Best Practices