Apps script to copy hyperlink to another sheet

Question

I'm new to javascript and need to copy 3 colums of a google spreadsheet to a second sheet (one column has hyperlink url value).

My main sheet "Responses" creates a new row of data when a website booking form is submitted. I need to copy the last row entry to the next sheet, "Sheet1".

The first sheet "Responses" should copy the last row, column K, M and column V (a website hyperlink). Pasted values should go in the next available row of "Sheet1" column A, B and C respectively, with the active hyperlinks in column C of "Sheet1".

I'm looking for javascript for google apps script, so I can put it on a macro/trigger, to copy the new entry to the next sheet automatically.

Can anybody help with the script please?

Answer 1

To get you started in using Google App Script with Spreadsheet, here is an example script that will do what you want. As a habit I always wrap my code in try {} catch {} block so when my project gets big I can tell where the error occured.

Code.gs

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Responses");
    // get all the values from the last row of Response.
    // in getRange() rows and columns start with 1.  column A is 1
    let values = sheet.getRange(sheet.getLastRow(),1,1,sheet.getLastColumn()).getValues();
    // for arrays the index starts with 0 so column K is 10
    let result = [values[0][10],values[0][12]];
    // hyperlinks are in Rich Text format, again V is column 22
    let hyper = sheet.getRange(sheet.getLastRow(),22).getRichTextValue();
    sheet = spread.getSheetByName("Sheet1");
    let lastRow = sheet.getLastRow()+1;
    sheet.getRange(lastRow,1,1,result.length).setValues([result]);
    sheet.getRange(lastRow,3).setRichTextValue(hyper);
  }
  catch(err) {
    console.log("Error in test: "+err)
  }
}

Reference

• SpreadsheetApp.getActiveSpreadsheet()
• Spreadsheet.getSheetByName()
• Sheet.getRange()
• Range.getValues()
• Range.getRichTextValue()
• Range.getLastRow()
• Range.setValues()
• Range.setRichTextValue()
• javascript Array