Macro to Reshape Data

[Roadeo]

Hi,

Wondering if someone can please help with with a data reshaping issue. I would like to create a macro to extract data from weekly files and append them to a master sheet. Here are the steps:

1) Each new file that comes in has several blocks of data pictured below. The vehicle number is oddly placed below the headers. I would like the macro to look for key words "Vehicle" and "Vehicle Totals".

2) Take the vehicle # and populate it in a separate column for each row of data between "Vehicle" and "Vehicle Totals" (in this case it would be 6 rows)

3) Copy the entire range of data between "Vehicle" and "Vehicle Totals" and append it to a master sheet on google sheets.

[Clark Lind]

Something like this should do the trick (adjust names to your sheet names):

async function addVehicleData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var ws = ss.getSheetByName("vehicle data");

  var ts = ss.getSheetByName("Master vehicle sheet");

  var vehicleData = ws.getDataRange().getDisplayValues();

  var vehicleID = await getVehicleID(vehicleData); //gets vehicle ID

  

  vehicleData = vehicleData.slice(2,-1); //removed first two rows and last row, isolating the data

  vehicleData.forEach( el => el.push(vehicleID)); //add vehicle ID to each data row

  vehicleData.forEach( el => ts.appendRow(el)); //adds each data row to master sheet

}

//isolates vehicle ID

function getVehicleID(data) {

data.forEach( (vehicle, i) => {

   if (vehicle.indexOf('VEHICLE') >= 0) { //if the row contains "VEHICLE", return the cell to the right (the ID)

    vehicleID = data[i][vehicle.indexOf('VEHICLE') +1]

    } 

 })

 return vehicleID;

} 

[Roadeo]

I will try this out. Thank you very much, appreciate your help.

[Roadeo]

So I have this code as below, but getting this error: TypeError: Cannot read property 'getDataRange' of null

How is the code accessing the google sheets files? Do I need to provide paths to them? I only specified the file names as instructed below and had them open. Please help.

async function addVehicleData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var ws = ss.getSheetByName("Test Statement");

  var ts = ss.getSheetByName("Settlement Statements");

  var vehicleData = ws.getDataRange().getDisplayValues();

  var vehicleID = await getVehicleID(vehicleData);  //gets vehicle ID

  

  vehicleData = vehicleData.slice(2,-1);  //removed first two rows and last row, isolating the data

  vehicleData.forEach( el => el.push(vehicleID));  //add vehicle ID to each data row

  vehicleData.forEach( el => ts.appendRow(el));  //adds each data row to master sheet

}

//isolates vehicle ID

function getVehicleID(data) {

data.forEach( (vehicle, i) => {

   if (vehicle.indexOf('VEHICLE') >= 0) {      //if the row contains "VEHICLE", return the cell to the right (the ID)

    vehicleID = data[i][vehicle.indexOf('VEHICLE') +1]    

      } 

   })

 return vehicleID;

} 

[Clark Lind]

Dumb question, did you insert the code into the Spreadsheet script editor, or did you create a new script file?

If it is a new script file, change the first variable from this:

var ss = SpreadsheetApp.getActiveSpreadsheet(); //assumes it is running inside a Spreadsheet

to this:

var ss = SpreadsheetApp.openById("??REPLACE WITH THE ID OF YOUR SHEET??");  //assumes you are running the script from outside of a Sheet, or within a different App.

Hope that helps!

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/SbR0cQYcuVE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7803f54e-3c38-4f49-b0d8-22323c978b84n%40googlegroups.com.

[Roadeo]

I'm not really sure. This is what I did: within the Master sheet, where I want all the data to be appended to, I clicked on Extensions and then Apps Script.

Which sheet ID should go into the highlighted Sheet ID below? The Master sheet or the Weekly sheet where the data is being retrieved from? I tried it both ways and when I put the Master Sheet I get the same error: Cannot read property 'getDataRange' of nul.

When I put the ID of the Weekly Sheet, it says it cannot recognize the ID.

var ss = SpreadsheetApp.openById("??REPLACE WITH THE ID OF YOUR SHEET??");  //assumes you are running the script from outside of a Sheet, or within a different App.

[Clark Lind]

The ID can be found in the URL.     It is the long string between /d/  and /edit. 

For example, if the code were in this file of mine:
https://docs.google.com/spreadsheets/d/1mjubX_9R39DYtuCBam1RsmYINTFrrKt7RWaCjaNwKt8/edit#gid=1578457889

I would copy the highlighted part and paste it into the code:
var ss = SpreadsheetApp.openById("1mjubX_9R39DYtuCBam1RsmYINTFrrKt7RWaCjaNwKt8"); 

Do the same with your spreadsheet. Or, if you'd like, you can use:
var ss =  SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());

If you use either of these, be sure to comment out the previous declaration:
// var ss = SpreadsheetApp.getActiveSpreadsheet();

[Roadeo]

Yes, that's where I got the spreadsheet ID. I've tried playing around with the code but having issues making it work. Below is the code that I've modified based on your suggestions. I'm getting the this error when trying to access the weekly statements sheet: Exception: Service Spreadsheets failed while accessing document with id 14UnVxN....

Also, I see the keyword "Vehicle" within the code below, but I'm not seeing "Vehicle Totals:". Maybe I'm missing it, but how would this process continue for each vehicle? As there are multiple blocks of this data as pictured below for each vehicle. So for each vehicle the rows would keep going down repeated in this same format and a way to determine where the data starts and ends for each vehicle is to use these keywords.

async function addVehicleData() {

  var ss = SpreadsheetApp.openById("1Mvjxzv8DlT..."); //Master Sheet

  var ws = SpreadsheetApp.openById("14UnVxNic..."); //Weekly Statements

  var ts = SpreadsheetApp.openById("1Mvjxzv8DlT..."); //Master Sheet

  var vehicleData = ws.getDataRange().getDisplayValues();

  var vehicleID = await getVehicleID(vehicleData);  //gets vehicle ID

  

  vehicleData = vehicleData.slice(2,-1);  //removed first two rows and last row, isolating the data

  vehicleData.forEach( el => el.push(vehicleID));  //add vehicle ID to each data row

  vehicleData.forEach( el => ts.appendRow(el));  //adds each data row to master sheet

}

//isolates vehicle ID

function getVehicleID(data) {

data.forEach( (vehicle, i) => {

   if (vehicle.indexOf('VEHICLE') >= 0) {      //if the row contains "VEHICLE", return the cell to the right (the ID)

    vehicleID = data[i][vehicle.indexOf('VEHICLE') +1]    

      } 

   })

 return vehicleID;

}

[Clark Lind]

If you want to share a sanitized version of your sheet, I'll take a look and see what is going on.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6ad463a1-6229-460c-b34a-fbcdff3bf845n%40googlegroups.com.

[Roadeo]

I've attached a file with mock data. There are two tabs in the file, but these will be two separate files within google sheets.

1) Master Sheet tab - this is where all data from weekly statements should be appended to. Right now it just has the headers with the metadata from the Weekly Sheet tab (row 5)

2) Weekly Sheet tab - this is how the structure of the statements that come in on a weekly basis which I would like to reshape and then append to the Master Sheet tab.

Please let me know if you have any questions. I really appreciate your help :)

[Clark Lind]

I created a new Spreadsheet and shared it with you. For others watching this thread, the code follows. Of interest was learning different ways to determine if a value is a valid Date.
The method I am using only works because of the second condition I'm using (vehicleFound). Any number passed to the isValidDate function will give a false positive.

//Add Menu Item to run the code

function onOpen() {

  SpreadsheetApp.getUi()

.createMenu("Trip data")

  .addItem("Process trip data","getVehicles")

  .addToUi()

}

//Short function to check if a value is an actual Date

//Used to check first column. If true, it is valid data.

function isValidDate(value) {

    var dateWrapper = new Date(value);

    return !isNaN(dateWrapper.getDate()); //returns true or false

}

function getVehicles() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var ws = ss.getSheetByName("Weekly Sheet");

    var ts = ss.getSheetByName("Master Sheet");

    var data = ws.getDataRange()

        .getDisplayValues();

    var tempVehicles = [];

    var id = "";

    var vehicleFound = false;  //used to determine if the row data is actual Trip data

    data.forEach((vehicle, i) => {  //look at each row

        if (vehicle.indexOf('VEHICLE') >= 0) { //use the word 'vehicle' as a starting point for valid data

            id = data[i][vehicle.indexOf('VEHICLE') + 1]; //if the word 'vehicle' is found, get the ID

            vehicleFound = true;  

        }

        if (isValidDate(vehicle[0]) && vehicleFound === true) {  //if col A is a valid date, and we found a vehicle/ID

            vehicle.push(id); //add vehicle ID to the trip

            tempVehicles.push(vehicle); //add the trip to an array

        }

        if (vehicle.indexOf('VEHICLE TOTALS:') >= 0) {  //vehicle data has stopped

            vehicleFound = false;   //stop adding non-trip data rows to the array

        }

    })

    //loop through each trip data row and append to Master Sheet

    tempVehicles.forEach(el => ts.appendRow(el));

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f555c1b9-b46a-49b5-ba23-82ae3811726bn%40googlegroups.com.

[Roadeo]

Thank you, thank you. This is amazing! Really appreciate your time and effort. Thank you again :)