google-apps-scriptgoogle-sheetssearchcopydata-transfer

How to efficiently transfer data from multiple cells in one Google Sheet row to a template on another google sheet with different cell arrangement

I have a google sheet (named "Template") which I use to capture client data. Data entry cells in this "Template" sheet are not arranged in a sequential manner due to the way that the template is designed. I have a code to save this data to a second google sheet serving as my database( named "Data"). I am writing a code to search the saved client records from "Data" by client unique identifier (client ID). After running the client record search function, I expect the data to be populated back to the original "Template". I can't seem to find an efficient way of achieving this using google apps script. The "Data" sheet will be having up to 50 columns of data per client.

Below is the code that I wrote. The code works as expected but I feel it's a long way of doing it and there might be a better and shorter way of achieving this when dealing with upwards of 50 columns per client. Is there a way to copy the entire row in "Data" sheet and paste the respective values to the range/array (["D3", "B1", "B2", "E2", B4",...] as in example below) in "Template" sheet using few lines of code, as opposed to setting value for each and every one of the 50 cells? Am still new to coding.

var ss = SpreadsheetApp.getActiveSpreadsheet();
templateS = ss.getSheetByName("Template");
dataS = ss.getSheetByName("Data");

//function to retrieve client record and populate template
function searchRecord() 

var searchCell = templateS.getRange("B6"); //cell holding the search value
var searchValue = searchCell.getValue(); // value to be used to search. Unique client ID. 


// Search record in data sheet using unique client ID
var recordFound = dataS.getRange("A:A") //client ID in column A of "Data"
                   .createTextFinder(searchValue) 
                   .matchCase(true)
                   .matchEntireCell(true)
                   .findNext();

var row = recordFound.getRow(); //capture row position containing the search value. 

//**populate template with up to 50 lines of below code**

templateS.getRange("D3").setValue(dataS.getRange(row, 1).getValue()); //capture client ID
templateS.getRange("B1").setValue(dataS.getRange(row, 2).getValue()); //capture title
templateS.getRange("B2").setValue(dataS.getRange(row, 3).getValue()); //capture surname
templateS.getRange("E2").setValue(dataS.getRange(row, 4).getValue()); //capture first name
templateS.getRange("B4").setValue(dataS.getRange(row, 5).getValue()); //capture address
}
Solution

  • I believe your goal is as follows.

    In this case, how about using Sheets API? When Sheets API is used, the process cost can be reduced a little. Ref When Sheets API is used to your script, how about the following modification?

    Modified script:

    Before you use this script, please enable Sheets API at Advanced Google services.

    In this sample, I used var dstRnages = ["D3", "B1", "B2", "E2", "B4"]; as the destination ranges. This is from your showing script. If you want to add more ranges, please modify it like var dstRnages = ["D3", "B1", "B2", "E2", "B4", "##", "##",,,,];. By this, the source ranges are automatically created by var srcRanges = [...Array(dstRnages.length)].map((_, i) => ('Data'!${columnIndexToLetter_(i)}${row}));. If you want to change this, please modify this.

    function searchRecord() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var templateS = ss.getSheetByName("Template");
  var dataS = ss.getSheetByName("Data");
  var searchCell = templateS.getRange("B6");
  var searchValue = searchCell.getValue();
  var recordFound = dataS.getRange("A:A")
    .createTextFinder(searchValue)
    .matchCase(true)
    .matchEntireCell(true)
    .findNext();

  // I modified the below script.
  if (!recordFound) return;
  var dstRnages = ["D3", "B1", "B2", "E2", "B4"]; // This is from your script.

  // Ref: https://stackoverflow.com/a/53678158
  const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : "";

  var row = recordFound.getRow();
  var ssId = ss.getId();
  dstRanges = dstRnages.map(e => `'Template'!${e}`);
  var srcRanges = [...Array(dstRnages.length)].map((_, i) => (`'Data'!${columnIndexToLetter_(i)}${row}`));
  var data = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: srcRanges }).valueRanges.map(({ values }, i) => ({ values, range: dstRanges[i] }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ssId);
}

    Note:

    References: