Update Google Apps Script Code on Google Sheet
Budget: $10 – $30 USD
I need a minor update in my Google apps script code. I wrote the original code and can do it but am extremely busy. Should only take 15 minutes.
Current Code looks like:
function myFunction() {
//1Yec2Y3FTsJGl1-y4GmTV-27TzBd4xOhx (alternate test folder with limited files is 11FitAFQ5v-2COHI_TkF0SgJaHC9LxYx7)
//https://www.youtube.com/watch?v=tmKqry4EKdQ
var folder=DriveApp.getFolderById("11FitAFQ5v-2COHI_TkF0SgJaHC9LxYx7")
var filesIterator= folder.getFiles();
var file;
var fileType;
var ssID;
var combinedData=[];
var data;
while(filesIterator.hasNext()){
file= filesIterator.next();
fileType= file.getMimeType();
if(fileType==="application/vnd.google-apps.spreadsheet"){
ssID=file.getId();
data=getDataFromSpreadsheet(ssID);
data=data.map(function(r){return r.concat([file.getName()]);}); //adds the filename as new column
data=data.map(function(r){return r.concat([getDataFromSpreadsheet(ssID)]);}); //adds the invoice total again as a new column
combinedData= combinedData.concat(data);
} //if ends here
} //while loop ends here
var ws=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("combined");
ws.getRange("A2:G").clearContent();
ws.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData);
}
function getDataFromSpreadsheet(ssID){
var ss= SpreadsheetApp.openById(ssID);
var ws = ss.getSheetByName("Invoice");
var data= [ws.getRange("Q44").getValues(),ws.getRange("Q47").getValues()]; //When you have " var data= [ws.getRange("Q44").getValues(),ws.getRange("Q47").getValues()]" in this line, it adds the invoice balance as a new row, figure out how to make it a new column instead. When you have " var data= [ws.getRange("Q44").getValues()]" then it just returns one row and one value (either the balance or the total. You should create a new tab, then run a function with one piece of data (Ex: the total) then put the blance on another tab, then join them together. Either that OR just apply some conditional formatting to rows where the total and balance rows DONT match. The goal is to batch import to pipedrive so the first option is better)
return data;
}
Just need to make a few tweaks so that the output data is formatted differently. Will send the google sheets over upon acceptance.
Current Code looks like:
function myFunction() {
//1Yec2Y3FTsJGl1-y4GmTV-27TzBd4xOhx (alternate test folder with limited files is 11FitAFQ5v-2COHI_TkF0SgJaHC9LxYx7)
//https://www.youtube.com/watch?v=tmKqry4EKdQ
var folder=DriveApp.getFolderById("11FitAFQ5v-2COHI_TkF0SgJaHC9LxYx7")
var filesIterator= folder.getFiles();
var file;
var fileType;
var ssID;
var combinedData=[];
var data;
while(filesIterator.hasNext()){
file= filesIterator.next();
fileType= file.getMimeType();
if(fileType==="application/vnd.google-apps.spreadsheet"){
ssID=file.getId();
data=getDataFromSpreadsheet(ssID);
data=data.map(function(r){return r.concat([file.getName()]);}); //adds the filename as new column
data=data.map(function(r){return r.concat([getDataFromSpreadsheet(ssID)]);}); //adds the invoice total again as a new column
combinedData= combinedData.concat(data);
} //if ends here
} //while loop ends here
var ws=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("combined");
ws.getRange("A2:G").clearContent();
ws.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData);
}
function getDataFromSpreadsheet(ssID){
var ss= SpreadsheetApp.openById(ssID);
var ws = ss.getSheetByName("Invoice");
var data= [ws.getRange("Q44").getValues(),ws.getRange("Q47").getValues()]; //When you have " var data= [ws.getRange("Q44").getValues(),ws.getRange("Q47").getValues()]" in this line, it adds the invoice balance as a new row, figure out how to make it a new column instead. When you have " var data= [ws.getRange("Q44").getValues()]" then it just returns one row and one value (either the balance or the total. You should create a new tab, then run a function with one piece of data (Ex: the total) then put the blance on another tab, then join them together. Either that OR just apply some conditional formatting to rows where the total and balance rows DONT match. The goal is to batch import to pipedrive so the first option is better)
return data;
}
Just need to make a few tweaks so that the output data is formatted differently. Will send the google sheets over upon acceptance.
Related categories:
Google App Engine