Copy Macro into Google Sheets-CodePudding

I've been working on a project, but I'm not as versed with Macros , and even less versed in Scripts. I've created an entry form on sheet "PM Entry" and the Macro for the button, copies all the cells in an array and pastes them on another sheet "PM Tracking" in a new row, so I can start building data from the entries. The Macro, works perfectly. Uploading to Google Sheets, of course the Macros don't transfer.

The trouble I'm having is transcribing the code from Macro to Script. I've gotten it to work a couple times, but not consecutively. This is the macro, I'm trying to turn into a script

    Sub SubmitButton()

Dim DB As Worksheet, SH As Worksheet
Dim TargetRow As Long, Index As Long
Dim SourceArr As Variant, DestArr As Variant
Dim Source As Range, Dest As Range

'set reference up-front
Set SH = ThisWorkbook.Worksheets("PM Entry")
Set DB = ThisWorkbook.Worksheets("PM Tracking")
With DB
    TargetRow = .Range("A" & .Rows.Count).End(xlUp).Row   1
End With
SourceArr = Array("D5", "H5", "D7", "D9", "H7", "H9", "D15", "D16", "D18", "H15", "H16", "H18", "D24", "D25", "D27", "H24", "H25", "H27", "D34", "D35", "D37", "H34", "H35", "H37", "D43", "D44", "D46")
DestArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA")

'loop through the source array, copying cell values to DB sheet
For Index = LBound(SourceArr) To UBound(SourceArr)
    Set Source = SH.Range(SourceArr(Index))
    Set Dest = DB.Range(DestArr(Index) & TargetRow)
    Source.Copy
    Dest.PasteSpecial (xlPasteValues)
Next Index

End Sub

Any help on this , would be greatly appreciated..

CodePudding user response：

Well, just in case. Probably this code will do the job:

function copyData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('PM Entry');
  var dest_sheet = ss.getSheetByName('PM Tracking');

  var cells = ["D5", "H5", "D7", "D9", "H7", "H9",
  "D15", "D16", "D18", "H15", "H16", "H18", "D24", "D25", "D27", 
  "H24", "H25", "H27",  "D34", "D35", "D37", 
  "H34", "H35", "H37", "D43", "D44", "D46"];

  var data = cells.map(c => sheet.getRange(c).getValue());

  dest_sheet.appendRow(data);
}