Apps Script tutorial: copying hyperlinks between cells on different sheets

Is there a way to copy data from one sheet to another while keeping all the links active? Currently, when I use this formula, it only copies the text.

I am unsure of what changes I need to make in order to copy all the data from the original sheet.

function SubmitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("ProyectStatus");
  var dataS = ss.getSheetByName("ToCompleteProyect");

  var values = [
    [formS.getRange("D1").getValue(), formS.getRange("D2").getValue(), formS.getRange("D3").getValue(), formS.getRange("D4").getValue(), formS.getRange("D5").getValue(), formS.getRange("D6").getValue(), formS.getRange("D7").getValue(), formS.getRange("D8").getValue(), formS.getRange("D9").getValue(), formS.getRange("D10").getValue(), formS.getRange("D11").getValue(), formS.getRange("D12").getValue(), formS.getRange("D13").getValue(), formS.getRange("D14").getValue(), formS.getRange("D15").getValue(), formS.getRange("D16").getValue(), formS.getRange("D17").getValue(), formS.getRange("D18").getValue(), formS.getRange("D19").getValue(), formS.getRange("D20").getValue(), formS.getRange("D21").getValue(), formS.getRange("D22").getValue(), formS.getRange("D23").getValue(), formS.getRange("D24").getValue(), formS.getRange("D25").getValue(), formS.getRange("D26").getValue(), formS.getRange("D27").getValue(), formS.getRange("D28").getValue(), formS.getRange("D29").getValue(), formS.getRange("D30").getValue(), formS.getRange("D31").getValue(), formS.getRange("D32").getValue(), formS.getRange("D33").getValue(), formS.getRange("D34").getValue(), formS.getRange("D35").getValue(), formS.getRange("D36").getValue(), formS.getRange("D37").getValue(), formS.getRange("D38").getValue(), formS.getRange("D39").getValue(), formS.getRange("D40").getValue(), formS.getRange("D41").getValue(), formS.getRange("D42").getValue(), formS.getRange("D43").getValue(), formS.getRange("D44").getValue(), formS.getRange("D45").getValue(), formS.getRange("D46").getValue(), formS.getRange("D47").getValue(), formS.getRange("D48").getValue(), formS.getRange("D49").getValue(), formS.getRange("D50").getValue(), formS.getRange("D51").getValue(), formS.getRange("D52").getValue()]
  ];
  dataS.getRange(dataS.getLastRow() + 1, 1, 1, 52).setValues(values);

  ClearCell();
javascriptgoogle-sheets

Answer №1

Project completion with Submit, Search, and Update buttons:

function ResetCell() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var formSheet = ss.getSheetByName("ProjectStatus");
    var RangeToReset = ["D1:D52"];
    for (var i = 0; i < RangeToReset.length; i++) {
        formSheet.getRange(RangeToReset[i]).clearContent();
    }
}
//-------------------------------------------------------------------------------
function SubmitData() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var formSheet = ss.getSheetByName("ProjectStatus");
    var dataSheet = ss.getSheetByName("ToCompleteProject");
    var lastRow = dataSheet.getLastRow() + 1
    formSheet.getRange("D1:D52").copyTo(dataSheet.getRange(lastRow, 1, 1, 52), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true)
    dataSheet.getRange(lastRow, 1).clearDataValidations()
    ResetCell();
}

//----------------------------------------------------------------------------

function Search() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var formSheet = ss.getSheetByName("ProjectStatus");
    var searchValue = formSheet.getRange("D1").getValue();
    var dataSheet = ss.getSheetByName("ToCompleteProject")
    var values = dataSheet.getDataRange();
    Logger.log(values.getLastRow())
    for (var i = 0; i < values.getLastRow(); i++) {
        var rowValue = dataSheet.getRange(i + 1, 1).getValue();
        if (rowValue == searchValue) {
            dataSheet.getRange(i + 1, 2, 1, 51).copyTo(formSheet.getRange("D2:D52"), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true)

        }
    }

}
//------------------------------------------------------------------------------
function Update() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var formSheet = ss.getSheetByName("ProjectStatus");
    var searchValue = formSheet.getRange("D1").getValue();
    var dataSheet = ss.getSheetByName("ToCompleteProject")
    var values = dataSheet.getDataRange();
    Logger.log(values.getLastRow())
    for (var i = 0; i < values.getLastRow(); i++) {
        var rowValue = dataSheet.getRange(i + 1, 1).getValue();
        if (rowValue == searchValue) {
            formSheet.getRange("D2:D52").copyTo(dataSheet.getRange(i + 1, 2, 1, 51), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true)
            ResetCell();
        }
    }
}

A simple way to achieve this is by copying and pasting not only the values but also transposing the cells as with Copy and Paste Special. A line of code that accomplishes this:

formSheet.getRange("D1:D52").copyTo(dataSheet.getRange(lastRow, 1, 1, 52), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true) ---> the TRUE value transposes the data

And the complete function:

function SubmitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSheet = ss.getSheetByName("ProjectStatus");
  var dataSheet = ss.getSheetByName("ToCompleteProject");
  var lastRow = dataSheet.getLastRow() + 1
  formSheet.getRange("D1:D52").copyTo(dataSheet.getRange(lastRow, 1, 1, 52), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true)
  ResetCell();
}

Previous Answer: If the links always appear in the same column of the last row, you can use this second function to handle them. I also suggested a simpler way to extract values from the form ;)

function SubmitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSheet = ss.getSheetByName("ProjectStatus");
  var dataSheet = ss.getSheetByName("ToCompleteProject");

  var values = formSheet.getRange("D1:D52").getValues() // Extract the entire column
  values = [values.map(function(n){return n[0]})] // Convert that column into a row
  
  var lastRow = dataSheet.getLastRow() + 1
  dataSheet.getRange(lastRow, 1, 1, 52).setValues(values);

  linkCellContents(dataSheet.getRange(lastRow, 10, 1, 1)) // Convert the value in column 10 (J) into a link... repeat for other columns

  ResetCell();
}

function linkCellContents(range) {
 var value = range.getValue()
 var richValue = SpreadsheetApp.newRichTextValue()
    .setText(value)
   .setLinkUrl(value)
   .build();
 range.setRichTextValue(richValue);
}

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

JavaScript - Reveal a div when a grid item is clicked

I have created a grid with a 5x7 layout using divs. I am trying to achieve a feature similar to the Netflix interface where, upon clicking on a div, a larger div will appear beneath it. This larger div should expand to 100% width of the parent container, p ...

javascripthtmlcsslaravel

Displaying and hiding the Angular <object> element

I am faced with a challenge involving managing files of various formats and creating a gallery with preview functionality. Everything works smoothly when clicking through items of the same format, like JPEGs. However, an issue arises when switching from vi ...

javascriptjqueryangularjsswfobject

Issue with state change in Component (React with Redux)

I am another person facing a similar issue. I have been unable to identify the error in my code yet. I suspect it has something to do with mutation, but despite trying almost everything, I am at a loss. My component does not update when I add new items to ...

javascriptreactjsredux

The functionality of the JavaScript animated placeholder seems to be malfunctioning

I am currently working on a code that updates the placeholder text every 2 seconds. The idea is to have it type out the letters one by one, and then erase them in the same manner. Unfortunately, the code is not functioning as expected. As a newcomer to J ...

javascriptjqueryarrays

jQuery Animation: Creative Menu Icon Display

Whenever the toggle menu's navigation icon is clicked, it triggers an animation that transforms the "hamburger" icon into an "X", causing the navigation menu to drop down. If a user clicks either the navigation icon or outside of the active toggle me ...

javascriptjqueryhtmlcss

How to import a JSON file in AngularJS using Node.js

I am currently working with NodeJS and AngularJS to handle some data. Within my Angular application, I am trying to access a JSON file and distribute its content into separate controllers. Right now, I am utilizing the http.get method within a controller ...

javascriptjsonnode.jsangularjs

Exploring the limitations of middlewares in supporting independent routers

When I examine the code provided, it consists of three distinct routers: const Express = require("express") const app = Express() // Three independent routers defined below const usersRouter = Express.Router() const productsRouter = Express.Router() cons ...

javascriptnode.jsexpressservermiddleware

Endless repetition occurs when invoking a function within a v-for loop

I've encountered an issue while trying to populate an array using a method, leading to redundant data and the following warning message: You may have an infinite update loop in a component render function. Below is the code snippet in question: ...

javascriptvue.jsvuetify.js

Upon pressing the browser's back button, the page fails to automatically load with the updated URL

Providing a little context: Let's say I initially access the page using URL1. After that, I use window.history.pushState() to change the URL to URL2. Now, when I click the "back" button, I observe that the address bar displays URL1 again, but the pa ...

javascriptbrowser

What is the best way to remove MongoDB collections by their age or date of creation?

I have multiple MongoDB collections created on different dates, each using the date as its name: 2015-06-01 2015-06-02 2015-06-03 ... 2015-06-30 total 30 collections If I only want to keep the latest 10 collections on a given day, for example today which ...

javascriptnode.jsmongodbmongoose

Utilize JSON text importing for template literals in Node.js

When it comes to my node js projects, I usually opt for using a text.json file and requiring it rather than hardcoding static text directly into my code. Here's an example: JSON file { "greet": "Hello world" } var text = require('./text.json ...

javascriptnode.js

Use a for loop to fill an array with values and then showcase its contents

I have been trying to figure out how to populate an array and display it immediately when targeting the route in my NodeJS project. Currently, I am able to console log a list of objects. However, I want to populate an array and show it when accessing loca ...

javascriptarraysnode.jsexpress

What is the best way to pass a div element and a variable from a Child component back to a Parent component in Next.js/React?

My goal is to create a webapp that, when an item in the list generated by the child component is clicked, displays the corresponding image in a div within the parent component. After some trial and error, I managed to generate the list in the child compone ...

javascriptreactjsnext.js

Employ CSS flexbox and/or JavaScript for creating a customizable webpage

I am in the process of developing a basic IDE for an educational programming language that has similarities to Karel the Dog. One major issue I am encountering is creating the foundation HTML page. The IDE consists of 4 main areas: Toolbox (contains but ...

javascripthtmlcssflexboxcodemirror

What are the drawbacks of relying on a dependent dependency in software development?

In a recent discussion on Stack Overflow, it was suggested that the best practice is to install packages from sub-dependencies for future use in the app, rather than directly from the dependencies node_modules folder. However, my scenario is a bit unique. ...

javascriptnpm

What is the process for retrieving my dates from local storage and displaying them without the time?

Having an event form, I collect information and store it in local storage without using an API. However, I face a challenge when extracting the startdate and enddate out of localstorage and formatting them as dd-mm-yyyy instead of yyyy-mm-ddT00:00:00.000Z. ...

javascriptjqueryhtmlangularjsdate

Enhancing link functionality with jQuery on a dynamically generated server page

I am facing an issue with my navigation menu that includes dropdowns. On desktop, the parent items need to be clickable as well, which is not a problem. However, for it to be responsive on mobile devices, I need to account for the lack of hover capability. ...

javascriptjqueryhtml

What is the best way to create a compound query in Firebase?

I am working on a TypeScript script to search for a city based on its population... import { getFirebase } from "react-redux-firebase"; ... get fb() { return getFirebase(); } get fs() { return this.fb.firestore(); } getCollection(coll ...

javascriptreactjsfirebasegoogle-cloud-firestore

Struggling to retrieve the most recent emitted value from another component in Angular?

Hello everyone, I am currently attempting to retrieve the most recent updated value of a variable from the component app-confirm-bottom-sheet in the app-bene-verification.ts component, but unfortunately, I am unable to achieve this. Below is the code snipp ...

javascriptangulartypescriptrxjs

Accessing Stencil through a corporate proxy network

As I embark on my inaugural Stencil project, I've encountered a puzzling error message: Cannot download "https://github.com/ionic-team/stencil- component-starter/archive/master .zip" Check your internet connection Error: connect ETIMEDOUT" De ...

javascriptgitnpmstenciljs