Is there a way to efficiently retrieve multiple values from an array and update data in a specific column using matching IDs?

Question

Is there a way to efficiently retrieve multiple values from an array and update data in a specific column using matching IDs?

In my Event Scheduler spreadsheet, I am looking for a way to efficiently manage adding or removing employees from the query table in column A. Currently, I have a dropdown list in each row to select names and a script that can only replace one name at a time on the database sheet upon clicking a button. However, this method is not practical when needing to update multiple names simultaneously. Is there a way to modify the function so it can replace multiple names at once instead of just one? Any assistance would be greatly appreciated. Below is the code snippet I currently have for replacing a single value.

function saveRecord7() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const formWS = ss.getSheetByName('Dashboard')
  const dataWS = ss.getSheetByName('Static_VDatabase_UID')
  const idCell = formWS.getRange('G7')
  const fieldRange =["AB7"]
  const clearcell = formWS.getRange('AB7')

  const id = idCell.getValue()
  if(id== ''){
    return
  }
  
   const cellFound = dataWS.getRange("A:A")
                  .createTextFinder(id)
                  .matchCase(true)
                  .matchEntireCell(true)
                  .findNext()

    if(!cellFound) return  
    const row = cellFound.getRow()            
  
   const fieldValues = fieldRange.map(f => formWS.getRange(f).getValue())
   fieldValues.unshift()
   dataWS.getRange(row,20,1,fieldValues.length).setValues([fieldValues])  

 clearcell.clearContent();
}

javascript arrays google-sheets google-apps-script foreach

Answer 1

Answer №1

function updateRecords() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const formSheet = spreadsheet.getSheetByName('Form');
  const dataSheet = spreadsheet.getSheetByName('Data');

  // Defining the ranges for IDs and values
  const idRange = formSheet.getRange('A1:A'); // Adjust the range as needed
  const valueRange = formSheet.getRange('B1:B'); // Adjust the range as needed

  const ids = idRange.getValues().filter(String); // Remove empty rows
  const values = valueRange.getValues().filter(String); // Remove empty rows

  if (ids.length !== values.length) {
    throw new Error('Mismatch between IDs and values length');
  }

  // Update records based on ID
  ids.forEach((idArray, index) => {
    const id = idArray[0];
    if (id === '') {
      return; // Skip empty IDs
    }

    const cellFound = dataSheet.getRange("A:A")
      .createTextFinder(id)
      .matchCase(true)
      .matchEntireCell(true)
      .findNext();

    if (!cellFound) return;
    const row = cellFound.getRow();

    // Update the value in a specific column
    dataSheet.getRange(row, 5).setValue(values[index][0]);
  });

  // Clear input ranges after updating records
  idRange.clearContent();
  valueRange.clearContent();
}

Answer 2

function updateRecords() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const formSheet = spreadsheet.getSheetByName('Form');
  const dataSheet = spreadsheet.getSheetByName('Data');

  // Defining the ranges for IDs and values
  const idRange = formSheet.getRange('A1:A'); // Adjust the range as needed
  const valueRange = formSheet.getRange('B1:B'); // Adjust the range as needed

  const ids = idRange.getValues().filter(String); // Remove empty rows
  const values = valueRange.getValues().filter(String); // Remove empty rows

  if (ids.length !== values.length) {
    throw new Error('Mismatch between IDs and values length');
  }

  // Update records based on ID
  ids.forEach((idArray, index) => {
    const id = idArray[0];
    if (id === '') {
      return; // Skip empty IDs
    }

    const cellFound = dataSheet.getRange("A:A")
      .createTextFinder(id)
      .matchCase(true)
      .matchEntireCell(true)
      .findNext();

    if (!cellFound) return;
    const row = cellFound.getRow();

    // Update the value in a specific column
    dataSheet.getRange(row, 5).setValue(values[index][0]);
  });

  // Clear input ranges after updating records
  idRange.clearContent();
  valueRange.clearContent();
}

Is there a way to efficiently retrieve multiple values from an array and update data in a specific column using matching IDs?

Answer №1

Similar questions

Utilizing $.getJSON to initiate a selection change event

Ajax fails to provide a response

Basic JavaScript string calculator

Binding arguments to child functions within Vue components

Creating a Dynamic Tree View Component in AngularJS Using JSON Data

An unexpected type error occurred: Unable to read the undefined property 'map' when utilizing Highcharts

Obtain form data as an object in Vue when passed in as a slot

Creating a Delicious Earth Progress Pie

Encountering a "Raphael is undefined" error message when working with Treant.js

Unable to modify page property status through the Notion API

Generate an array resembling a list when displayed in PHP

The ajaxStart() and ajaxStop() methods are not being triggered

Automatically filling in related information in multiple input fields after choosing a value in a specific input field using JavaScript

loading JSON file using dynamic JavaScript techniques

JavaScript: Creating a Function that Returns an Object with an Undefined `this.variable`

Blending Angular5 and AngularJS in Polymer

Update the package.json file by adding a new command to an existing script

Retrieve a div element using two specific data attributes, while excluding certain other data attributes

Live Update Google Sheet Data to JSON Without Reloading Web Page

Traversing through nested arrays within nested objects using AngularJS