GAS transfer data

AppSheet AppSheet Q&A
,
1

I’m trying to create an application for storing oxygen cylinders, and I’m struggling with implementing code that will transfer values from a sheet named “AUTOMATION OF APPLICATIONS” to a sheet named “Bottle Table”. I would like the columns Location, Date and Time of Entry, Responsible Person, Quantity Issued, Bottle Capacity, and Owner to be transferred 1:1. However, I want the “Barcode” column in the “Bottle Table” sheet to take its values from the “Scanned Barcodes” column in the “AUTOMATION OF APPLICATIONS” sheet, where this column lists all barcode values in one cell, separated by commas. I would like it to take one barcode at a time and create an entry from it.

here’s the code that im using currently i GAS:

function onChange(e) {
var sourceSheetId = “1jhMrEzaD5G_oPY4MtT-A5tm_xtJNRDyFz0Grd2fkKjo”;
var targetSheetId = “1y9f811D0BrZpWpNjpFvWsaa_fSYH-oF0pITP5sDvT1k”;

var sourceSheet = SpreadsheetApp.openById(sourceSheetId);
var targetSheet = SpreadsheetApp.openById(targetSheetId);

// Check if changes are related to the source sheet
var changesRelatedToSourceSheet = e.source.getSheet().getSheetId() === sourceSheet.getSheetId();

if (changesRelatedToSourceSheet) {
// Get data for the newly added row
var newRow = e.range.getRow();
var newDataForRow = sourceSheet.getRange(newRow, 1, 1, sourceSheet.getLastColumn()).getValues()[0];

// Transfer data to the target sheet
var location = newDataForRow[0];
var dateTime = newDataForRow[1];
var person = newDataForRow[2];
var quantityIssued = newDataForRow[3];
var capacity = newDataForRow[4];
var owner = newDataForRow[5];
var barcodeValues = newDataForRow[6].split(', ');

// Transfer data for each barcode
for (var j = 0; j < barcodeValues.length; j++) {
// Find the last row in the target sheet
var lastRow = targetSheet.getLastRow() + 1;

// Transfer data to the target sheet
targetSheet.getRange(lastRow, 1).setValue(location);
targetSheet.getRange(lastRow, 2).setValue(dateTime);
targetSheet.getRange(lastRow, 3).setValue(person);
targetSheet.getRange(lastRow, 4).setValue(quantityIssued);
targetSheet.getRange(lastRow, 5).setValue(capacity);
targetSheet.getRange(lastRow, 6).setValue(owner);
targetSheet.getRange(lastRow, 7).setValue(barcodeValues[j]);
}
}
}

Tried to do looping with actions, but seems im to stupid for that :P.

2

Is your target sheet by chance a datasource in an Appsheet application? If so I might suggest altering the script to create a rows array that can be written to Appsheet via API. If the target sheet is empty currently then I would suggest creating the entire data array in the script first and then writing it to the target sheet in one call vs setting each row and each column individually. What errors does the script throw or what is not functioning.

3 
function onChange(e) {
  var targetSheetId = "1y9f811D0BrZpWpNjpFvWsaa_fSYH-oF0pITP5sDvT1k";

  var targetSheet = SpreadsheetApp.openById(targetSheetId).getSheetByName('YourSheetName');

  // Get data for the newly added row
  var newRow = e.range.getRow(); //this should return the entire row in array format []

  var barcodeValues = newRow.pop().split(', '); //this removes the last element of the newRow and then splits it into an array based on comma split

  // Transfer data for each barcode
  barcodeValues.forEach((barcode) => {
    var modifiedRow = newRow.push(barcode); //newRow is a shortened array because of previous pop() function, push adds the individual barcode at the end of the array
    targetSheet.appendRow(modifiedRow); //row is added below the last row in the target sheet
  });
}

This would be my proposed solution. Untested since I have no example data to work with. This also assumes that the onChange function is triggered by an onchange trigger in the source sheet. No guarantees that this will work in your case since I don’t know a single detail about your process.

1 Like
4

I figured it out using Advancesplit formula with gas code I Google sheets

1 Like
5

Glad you figured it out. I may still suggest taking a look at my proposed code solution as you might be able to make your own code more efficient. Especially take note of the appendRow function within the SpreadsheetApp GAS capability. There are also a lot of JS array functions like pop() and push() that can help along the way to reconstruct arrays.

2 Likes